SELECT
TABLE
WHERE
-- the rows I want
( ( COLUMN2 = a AND COLUMN3 = b ) OR ( ( COLUMN2 = c AND COLUMN3 = d ) )
COLUMN1 NOT IN ( ... ) -- The rows that I don't want
The combinations for COLUMN2 and COLUMN3 can be somewhere between 20000 and 60000 I think, though the exact number is not known at this time.
The values for COLUMN2 and COLUMN3 does not follow a pattern, so it is not possible to simplify the query enough so that the problem is avoided altogether.
This values in the IN expression can also reach be about the same number.
--- Edit 2
Solution:
This was the solution I ended up with. As least until any new problems crop up.
First the query is split into several queries. Together all the queries cover the rows to select. This avoids the condition limit in SQL Server and also seems to greatly improve efficiency in the cases where the limit wasn't reached as well.
Then result from each query is joined using UNION.
The total result is then filtered by the application instead of the SQL Server. This seems to be reasonably efficient in this case. If at a later time this proves to be to inefficient, this can probably be replaced by the solution outline in the answer below.
If you really need thousands of conditions on a query, and can't simplify it using ranges you may try joining in a temp table. Create a temp table with the values in it and then INNER JOIN to this in your query, which will filter on those values in the table.
from something like (only speculation, OP has provided no example code):
SELECT
FROM MyTale
WHERE (a=5 AND b='apple' AND c=1024)
OR (a=3 AND b='pear' AND c=2048)
OR (a=8 AND b='apple' AND c=2048)
CREATE TABLE #TempJoin
(a int
,b char(10)
,c int
INSERT INTO #TempJoin VALUES (5,'apple',1024)
INSERT INTO #TempJoin VALUES (3,'pear',2048)
INSERT INTO #TempJoin VALUES (8,'apple',2048)
SELECT
FROM MyTale m
INNER JOIN #TempJoin t ON m.a=t.a AND m.b=t.b AND m.c=t.c
EDIT based on OP's edit...
I'm still very perplexed with "why" so many conditions. I just can't imagine why, could you give a little background on the nature of the problem you are trying to solve?
Where are all all of the values used in the conditions coming from? user input, a file, another database table?
There may be a very simple solution that involves doing things differently, which results in not needing to have thousands of conditions in your query.
The only way to solve your problem (as presented) is to store your conditions as rows in two tables: RowsToKeep and RowsToRemove. You can use regular tables, #temp tables, or ##globalTemp tables. You don't give enough info to recommend which to use (how often does this query run? will there be multiple similar queries running at the same time, etc..) In my example code, I'll use a regular table, and assume that nothing will interfere with the regular tables (no one else will try to use/modify RowsToKeep and RowsToRemove while this query is being prepared or being run).
CREATE TABLE RowsToKeep
(a int --you don't say the data type, but you can make these anything
,b char(10) --you don't say the data type, but you can make these anything
CREATE TABLE RowsToRemove
(c int --you don't say the data type, but you can make these anything
For a query such as you describe, I'd guess that you are building it dynamically using loops. TRUNCATE or DELETE the two tables first, then as you loop INSERT INTO these tables instead of building a dynamic query conditions. After inserting all the values, You can then use the following simple query, which will function the same as your original, but not be limited by number of conditions:
SELECT
FROM TABLE t
INNER JOIN RowsToKeep k ON t.Column2=k.a AND t.Column3=k.b
LEFT OUTER JOIN RowsToRemove r ON t.Column1=r.a
WHERE r.a IS NULL
–
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
