Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    6

    Unanswered: NOT IN slows query down the first time but subsequent times

    I have an UPDATE query with several restrictions including a huge NOT IN containing about two hundred two-character strings. My query is taking about a minute to run the first time it is called from my ODBC application then runs in no time at all subsequent times. If I comment out the NOT IN clause then it runs quickly the first time as well as in subsequent calls. On Oracle the same query performs just as fast the first time as it does subsequent times. This is suggesting to me that SQL Server is perhaps creating a temporary table with all the two-character strings and caching it for all successive calls. My customers typically only run the query once so would prefer if it ran quickly first time. Is there a faster way to perform the NOT IN restriction? My app allows for one query to be run if the user is running SQL Server and another one on Oracle. TIA.

    Code:
    SELECT
        col1,
        col2...
    FROM
        my_table
    WHERE
        a = b
     ...
        x = y
    AND z NOT IN ('AA', 'AB' ... 'QG', 'ZZ')
    Last edited by zeolite; 03-11-11 at 13:31.

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    create a temp table and index the column, change the where clause to be NOT EXISTS (select 1 from temp table joining against z)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •