Select query runs forever in sql 2005

  • By this time we had put approximately 20 indexes on other tables identified by DTA and the "missing indexes" dmv's that didn't really help the main problem

    Sorry , I did some googling and I kind of see what you mean now

  • I have witnessed many times that NOT IN works very slow in SQL Server 2005, especially when the it is comparing with a long list of string values.

    Didn't knew anbout SQL Server 2000 (as in current scenario).

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Christopher Stobbs (4/15/2009)


    oh and here is some sample stuff that I'm using:

    CREATE TABLE Table1

    (BIBNAC VARCHAR(100),Col1 INT,Col2 INT ,Col3 INT)

    CREATE TABLE Table2

    (ACCOUNTNUM VARCHAR(100))

    CREATE CLUSTERED INDEX PK_1 ON Table1

    (BIBNAC)

    CREATE CLUSTERED INDEX PK_1 ON Table2

    (ACCOUNTNUM)

    INSERT INTO Table1

    SELECT TOP (10000) 41,1,0,2

    FROM syscomments a, syscomments b UNION ALL

    SELECT TOP (10000) 1,99,12,3

    FROM syscomments a, syscomments b UNION ALL

    SELECT TOP (10000) 0005041,99,12,3

    FROM syscomments a, syscomments b UNION ALL

    SELECT TOP (10000) 00074441,99,7,3

    FROM syscomments a, syscomments b UNION ALL

    SELECT TOP (10000) 00000001,9,2,32

    FROM syscomments a, syscomments b UNION ALL

    SELECT TOP (10000) 0021,63,731,31

    FROM syscomments a, syscomments b

    INSERT INTO Table2

    SELECT TOP 10000 41

    FROM syscomments a, syscomments b UNION ALL

    SELECT TOP 10000 36345

    FROM syscomments a, syscomments b UNION ALL

    SELECT TOP 10000 002232

    FROM syscomments a, syscomments b UNION ALL

    SELECT TOP 10000 000234242

    FROM syscomments a, syscomments b UNION ALL

    SELECT TOP 10000 00000211

    FROM syscomments a, syscomments b UNION ALL

    SELECT TOP 10000 1

    FROM syscomments a, syscomments b

    SELECT COUNT(*) FROM Table1

    SELECT COUNT(*) FROM Table2

    UPDATE STATISTICS Table1 WITH FULLSCAN

    UPDATE STATISTICS Table2 WITH FULLSCAN

    select *

    from Table1 T1

    where substring(T1.BIBNAC,patindex('%[^0]%', T1.BIBNAC),len(T1.BIBNAC) - patindex('%[^0]%', T1.BIBNAC) + 1) in

    (select

    substring(T1A.BIBNAC,patindex('%[^0]%', T1A.BIBNAC),len(T1.BIBNAC) - patindex('%[^0]%', T1A.BIBNAC) + 1)

    from Table1 T1A

    except

    select

    substring(T2.ACCOUNTNUM,patindex('%[^0]%', T2.ACCOUNTNUM),len(T2.ACCOUNTNUM) - patindex('%[^0]%', T2.ACCOUNTNUM) + 1)

    from Table2 T2)

    select * from Table1 where

    substring(BIBNAC,patindex('%[^0]%', BIBNAC),len(BIBNAC) - patindex('%[^0]%', BIBNAC) + 1)

    NOT IN

    (

    SELECT

    substring(accountnum,patindex('%[^0]%', accountnum),len(accountnum) - patindex('%[^0]%', accountnum) + 1)

    FROM

    Table2

    )

    When I add my data to your tables the 'except' query takes forever in 2005.

    I have officially gone mad with this problem!

  • Hi,

    Have you tried my NOT EXISTS solution in 2005?

    I found that to be the fastest...

    select *

    from Table1

    where NOT EXISTS

    (

    SELECT 1

    FROM Table2

    WHERE substring(BIBNAC,patindex('%[^0]%', BIBNAC),len(BIBNAC) - patindex('%[^0]%', BIBNAC) + 1) =

    substring(accountnum,patindex('%[^0]%', accountnum),len(accountnum) - patindex('%[^0]%', accountnum) + 1)

    )

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Yeah i dunno man, i tried the NOT EXISTS method and it works with your dummy data but not with my actual data.

    Maybe its a data thing.

  • Strange, but something to check, are there any NULL values in Table2 (the one being queried in the WHERE ... NOT IN ?

  • No unfortunately there aren't any null values

  • I ran into the same problem when we upgraded to SQL2K5. It does in fact process a NOT IN query very different than SQL2K especially if the subquery is pulling a "large" list. My jobs were getting slammed. I had to change all the NOT IN queries to LEFT JOIN queries to get the same performance, and in some cases it was even faster. As everyone said prior, the execution plan will elude to the possible differences.

  • Thank Antonio

    Yeah seems like a join instead of 'not in' is the way to go

Viewing 9 posts - 61 through 68 (of 68 total)

You must be logged in to reply to this topic. Login to reply