DevForce Community Forum : Subquery too deep http://www.ideablade.com/forum/ This is an XML content feed of; DevForce Community Forum : DevForce Classic : Subquery too deep Thu, 11 Jun 2026 21:11:48 -700 Mon, 10 Sep 2007 08:49:08 -700 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 9.69 360 www.ideablade.com/forum/RSS_post_feed.asp?TID=320 DevForce Community Forum http://www.ideablade.com/forum/forum_images/IdeaBlade_logo_tm.png http://www.ideablade.com/forum/ Subquery too deep : I think you should be able to... http://www.ideablade.com/forum/forum_posts.asp?TID=320&PID=1132#1132 Author: m2r4miller
Subject: 320
Posted: 10-Sep-2007 at 8:49am

I think you should be able to rewrite the query like this to get the same results:

... where [ThisID] in (Select [ID] from [Bar] where [foo] in (A, B, C, D, E ...))
 
That streamlines the SQL generated which should allow a great deal more possibilities for [foo].
 
-Mark
]]> Mon, 10 Sep 2007 08:49:08 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=320&PID=1132#1132 Subquery too deep : .NET has a limit that used to... http://www.ideablade.com/forum/forum_posts.asp?TID=320&PID=745#745 Author: pkarsh
Subject: 320
Posted: 20-Jul-2007 at 5:13pm

.NET has a limit that used to be 8000 characters to a query. I don't know offhand if the limit is higher under .NET 2.0. I suspect that the limit you ran into was not in the length of subquery clauses, but rather in the length of the SQL query that was generated from your query. IN clauses can generate this problem because the SQL that is generated will add a term for each result of the IN clause. If there are a large number of terms that satisfy that clause, that can drive the number of characters beyond .NET's limit.

It looks like you discovered the correct workaround yourself.

You can see the SQL that is generated by an application by looking in the DebugLog that by default is created in the directory where the executable runs.
]]> Fri, 20 Jul 2007 17:13:17 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=320&PID=745#745 Subquery too deep : During some development, I ran... http://www.ideablade.com/forum/forum_posts.asp?TID=320&PID=738#738 Author: gjsdb
Subject: 320
Posted: 20-Jul-2007 at 9:52am

During some development, I ran into an error message regarding a Subquery (or set of Subqueries).
 
In effect, I was adding Subqueries to Subselect items from a query such that:
 
... where [ThisID] in (Select [ID] from [Bar] where ([foo] = A) or ([foo] = B) or ([foo] = C) .... and they tell two friends and they tell two friends...
 
The runtime error implied that the subquery was "too deep."  This was confusing because the number of Subqueries in my application had not changed.  However, the number of Clauses added to the Subquery had increased (dramatically).
 
I throttled the number of clauses that are added to any subquery and the error went away.
 
However, this raises a question.  What is the "length," if that's what one can call it, of a set of subquery clauses?  I think this is the cause of my error.  I think I was adding too many clauses to a particular subquery and the length was too much.
 
Does anyone have any experience or ideas?
 
I corrected the problem (probably caused by a bad pattern on my part) but I'm still curious.
 
]]> Fri, 20 Jul 2007 09:52:57 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=320&PID=738#738