Print Page | Close Window

Subquery too deep

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=320
Printed Date: 30-Aug-2025 at 1:37am


Topic: Subquery too deep
Posted By: gjsdb
Subject: Subquery too deep
Date 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.
 


Replies:
Posted By: pkarsh
Date 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.

Posted By: m2r4miller
Date 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


Print Page | Close Window