SQL Server Performance

Limitation on IN clause

Discussion in 'T-SQL Performance Tuning for Developers' started by sumeshs, May 12, 2005.

  1. sumeshs New Member

    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
  2. ChrisFretwell New Member

    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?

  3. dineshasanka Moderator

    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,

  4. FrankKalis Moderator

    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
  5. surendrakalekar New Member

    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?
  6. Adriaan New Member

    Not sure which type of IN clause is meant in the original post: IN (&lt;val1&gt;,&lt;val2&gt;, &lt;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.
  7. derrickleggett New Member

    You might want to look at this interesting KB. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /<a target="_blank" href=http://support.microsoft.com/kb/816069/>http://support.microsoft.com/kb/816069/</a><br /><br />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.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.

Share This Page