question

virendrasingh_77 avatar image
virendrasingh_77 asked

Query too complex - error message

How do I run a query which is heavy in criteria in the WHERE clause? Example: in (1,2,3,4,5,6,7...) without it giving the error "query too complex"?
query-results
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you post the actual query, and tell us a bit about what it is intended to do, and how the query is being generated (for example is the WHERE clause being built by parameters passed into an application)? Did you know that "WHERE SomeId IN(1,2,3,4,5,6,7....)" is just shorthand for "WHERE SomeId = '1' or SomeId = '2' or SomeId = '3'... etc".
0 Likes 0 ·
GPO avatar image
GPO answered
You could select the criteria into a temporary data structure (table variable, temp table) or maybe a CT, and join to that. For example: --======= Put some dummy data into a temp table. This represents the table you're currently querying. IF OBJECT_ID('tempdb..#my_table') IS NOT NULL DROP TABLE #my_table ; SELECT 1 as my_table_id,'ABC' as my_table_description,1 as some_id INTO #my_table UNION ALL SELECT 2,'BBC',9 UNION ALL SELECT 3,'CBC',10 UNION ALL SELECT 4,'DBC',10 UNION ALL SELECT 5,'EBC',19 UNION ALL SELECT 6,'FBC',19 UNION ALL SELECT 7,'GBC',16 UNION ALL SELECT 8,'HBC',13 UNION ALL SELECT 9,'IBC',16 UNION ALL SELECT 10,'JBC',16 UNION ALL SELECT 11,'KBC',16 UNION ALL SELECT 12,'LBC',16 UNION ALL SELECT 13,'MBC',1 UNION ALL SELECT 14,'NBC',2 UNION ALL SELECT 15,'OBC',7 ; --======= The data in this table represent what would have gone in your in(1,2,3,4,5...) IF OBJECT_ID('tempdb..#criteria') IS NOT NULL DROP TABLE #criteria ; SELECT 1 as some_id INTO #criteria UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 /*and so on*/ ; --======= Inner join them, (inner join works as a filter). SELECT mt.my_table_id ,mt.my_table_description FROM #my_table mt JOIN #criteria cr ON mt.some_id = cr.some_id ; DISCLAIMER: This in isolation might solve your immediate problem, BUT it should not be construed as a good solution. This is because we don't know what your underlying business problem is, and what scope there may be for other, better solutions to that underlying problem. The fact that you have so much in your WHERE clause that you're generating a "query is too complex" error is a red flag that there is likely to be a more fundamental problem going on. If this answers your question, please mark it as such for the benefit of others and the ongoing viability of the forum.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

this is not possible to apply this solution in our database bcoz there are so many procedures using "in " in where clause .so suggest me if there any setting so that it may be apply without any changes.
0 Likes 0 ·
kevaburg avatar image
kevaburg answered
Consider taking the IN clause out and replacing it with a LIKE or a more specific exception clause (WHERE x = y). As mentioned above, using a CTE query might help but then as also mentioned, we haven't seen any Code to be able to help any further.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.