Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries

Question

I have the folloiwng method inside my asp.net mvc web application and i am using Entity framework as the data access layer:-

        public IEnumerable<AaaUserContactInfo> getcontactinfo(long[] id)
        {
var organizationsiteids = (from accountsitemapping in entities.AccountSiteMappings
where id.Any(accountid => accountsitemapping.ACCOUNTID == accountid)
select accountsitemapping.SITEID).ToList();

var usersdepts = from userdept in entities.UserDepartments
join deptdefinition in entities.DepartmentDefinitions on userdept.DEPTID equals deptdefinition.DEPTID

where organizationsiteids.Any(accountid => deptdefinition.SITEID == accountid)

var contactsinfos = from contactinfo in entities.AaaUserContactInfoes 
                                join userdept in usersdepts on  contactinfo.USER_ID equals userdept.USERID

                                 select contactinfo;

            return contactsinfos;

But if the number of records are huge then i will get the folloiwng error:-

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +388 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +688 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4403 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +82 System.Data.SqlClient.SqlDataReader.get_MetaData() +135 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6665229 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +6667096 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +577
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +107
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +288
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +180
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +689

While if the number of records returned are small then the code will work fine , so what might be the problem?

@RobertHarvey I would guess that the OP would like help in determining what that error actually means and how he goes about correcting the issue. It may be obvious to you how to debug and correct that, but it may not be obvious to others. — Colin Mackay, Jan 05 '13 at 19:55
but i canot understand how come a query work fine if the returned data is small while it will raise an exception if the returned data is somehow huge !!! — John John, Jan 05 '13 at 20:13
Why do you find that so surprising? As vast as the resources (memory, processing power and hard disk space) inside a computer are compared to just a few years ago, they are still not limitless. — Robert Harvey, Jan 05 '13 at 23:06

score 3 · Answer 1 · answered Jan 06 '13 at 18:17

3

Per the answer to the duplicate question mentioned above in the comments, try the following as the where clause of your first query, since it's most likely that that's the one giving all of the trouble:

where id.Contains(accountsitemapping.ACCOUNTID)

answered Jan 06 '13 at 18:17

Corey Adler

15,897
18
66
80

i have found this helpful in several cases. – Brian Sweeney Jan 24 '14 at 20:36
+1 This is the answer that fixed it for me. I have just learnt that I should use Contains rather than Any. This does not seem right to me, but it works. – arame3333 Nov 06 '15 at 09:12

score 1 · Answer 2 · answered Jan 05 '13 at 20:09

I'd be tempted to remove all the Joins in there and do the lookups seperately.

It might seem counter productive that way, but if you

var organizationsiteids = (from accountsitemapping in entities.AccountSiteMappings
where id.Any(accountid => accountsitemapping.ACCOUNTID == accountid)
select accountsitemapping.SITEID).ToList();

Then keeping that in memory, loop over that collection and use it to get the rest of the details from your DB, it will both simplify the query, and likely solve the error.

As way of an answer to your actual question however, the SQL that that's getting generated most likely is generating an SQL join behind the scenes that has to many join conditions, and or nested queries for your SQL server to handle.

There are 2 things you can do to help figure this out.

1) If you have access tot he SQL server being used, use the SQL Server Profiler tool, and look to see what SQL code is being generated. or 2) (I can't remember off top of my head how to do this) but get EF/L2S or what ever your using to output the SQL to the debug / output windows in visual studio.

One last thing you might want to do.

Download LinqPad ( http://www.linqpad.net/ ) and use that to reproduce the query in your code, that will then allow you to sit and play with the Linq Statement(s) in a sandbox, to help you understand what's going on.

I can't be much more help than that unless you can post the SQL that's being generated ( Option 1 is the preferred way ) plus I'm logging off the internet for the night now :-)

hope you can give me some hint on how to solve this, as i have never use SQL profiler to see the generated sql, but i can not understand how my code will raise an exception if the number of records are large while if the number of records are small then it will work fine — John John, Jan 05 '13 at 20:51
Can you add the SQL generated by profiler to your question, that would be the first step. That way myself and others can see what's being generated that's upsetting your database. — shawty, Jan 06 '13 at 10:24
Also this stack overflow post : http://stackoverflow.com/questions/13442065/some-part-of-your-sql-statement-is-nested-too-deeply?rq=1 : will give you some pointers too. — shawty, Jan 06 '13 at 10:27

score 0 · Answer 3 · answered Aug 12 '14 at 14:00

0

Another solution, are iterate over the records getting the another ones and aggregate to an final list.

It's not fastest, not beauty, but will solve.

answered Aug 12 '14 at 14:00

Tiago Gouvêa

15,036
4
75
81

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries

3 Answers3

Related