New Posts New Posts RSS Feed: Subquery too deep
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Subquery too deep
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
gjsdb View Drop Down
Newbie
Newbie
Avatar

Joined: 14-Jun-2007
Location: United States
Posts: 1 		Post Options Post Options   Quote gjsdb Quote  Post ReplyReply Direct Link To This Post Topic: Subquery too deep
    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.
 
Back to Top
pkarsh View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 14-Jun-2007
Location: United States
Posts: 32 		Post Options Post Options   Quote pkarsh Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
m2r4miller View Drop Down
Newbie
Newbie
Avatar

Joined: 19-Aug-2007
Location: United States
Posts: 12 		Post Options Post Options   Quote m2r4miller Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down