SQL Server Performance Forum – Threads Archive

Limitation on IN clause

Are there any limitations on the number of expressions in an IN clause? I have heard EXISTS is faster than IN … We have a query from the front end which puts together more than 1000 values in an IN clause. What do you think? Thanks for your help,
Sumesh
Prior preparation prevents poor performance

Exists is almost always faster – this of course is dependent on the index(es) in the table contained in the exists clause. In some cases, depending on the tables involed, a join can work too. Can you give more information, this doesnt tell much. What is the query that puts the values in the in clause? How does this relate to your select query etc?

Yes Generally EXISTS is faster than IN, But it all depends your indexes

quote:Originally posted by sumeshs Are there any limitations on the number of expressions in an IN clause? I have heard EXISTS is faster than IN … We have a query from the front end which puts together more than 1000 values in an IN clause. What do you think? Thanks for your help,

I think, this depends on some of the parameters Chris mentioned AND on how many rows you can expect to match. Basically you have three alternatives at hand
USE pubs
GO
SELECT DISTINCT city
FROM authors
WHERE NOT EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city) SELECT DISTINCT authors.city
FROM authors
LEFT JOIN publishers
ON authors.city = publishers.city
WHERE publishers.city IS NULL SELECT DISTINCT city
FROM authors
WHERE city NOT IN
(SELECT city
FROM publishers) to find (non)existant data. If there is a larger number of matching rows, a JOIN might be preferable. If there are only a few rows to match, EXISTS might be faster. If this is a crucial query for your system, I would set up a test scenario and play with it. Anyway, here are some informations on using a large IN clause:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;843534
http://support.microsoft.com/default.aspx?scid=kb;EN-US;815183 —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

Frank,
Using In works slow, when the numbers of rows in subquery are more. In that case if we used the #temp table in subquery and search the record by using In operator, will it work better than normal In subquery?

Not sure which type of IN clause is meant in the original post: IN (<val1>,<val2>, <etc&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> or IN (SELECT …). The first type is harder to maintain if the list changes: easier to store that in a lookup table, perhaps as a predicate that a subquery can filter on without having to spell out the lookup keys like in the first type.

You might want to look at this interesting KB. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] http://support.microsoft.com/kb/816069/</a> This is something fixed in SP4 and the hotfix detailed in the article. Notice that concurrency issues can be experienced with "queries that have large IN clause lists (with hundreds to thousands of values)". You can obviously get really, really large IN clauses. The real questions should be "is it good design", which is what a lot of people have alluded to in the thread. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

]]>