Select query runs forever in sql 2005

  • Rowan (4/15/2009)


    Thanks Lynn, i will try that asap

    I did try your query where you use 'except'

    This did cut the running time down quite a bit

    Not really sure what "quite a bit" means. Was it still considerably longer than the 2000 version?

  • mmm I'm going crazy here...

    Ok with the following query I can confirm that on 200 it takes milliseconds and in 2005 it takes well over 5 minutes and waiting.

    I've done full stats updates etc.

    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

    )

    with this query in 2005 it takes milliseconds

    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)

    I'll keep looking or waiting should I say until I get exec plans to do more comparisions

    ----------------------------------------------
    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
  • 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

    )

    ----------------------------------------------
    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
  • Christopher Stobbs (4/15/2009)


    mmm I'm going crazy here...

    Ok with the following query I can confirm that on 200 it takes milliseconds and in 2005 it takes well over 5 minutes and waiting.

    I've done full stats updates etc.

    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

    )

    with this query in 2005 it takes milliseconds

    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)

    I'll keep looking or waiting should I say until I get exec plans to do more comparisions

    Chris,

    Can you post the DDL for the tables you built along with how you created the test data?

  • OK here is another solution that seems super fast, it also looks like less IO than lynns.

    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)

    )

    One thing I have noticed not sure how valid this is but the results in 2000 are return in a different order...

    Lynn code is above... still haven't got any results so no more news from this side

    ----------------------------------------------
    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
  • Chris, Nevermind. I found your post above.

    The second query, even with OPTION (MAXDOP 1), is pegging my Dual Proc Quad Core system.

    The first query ran extremely fast. I can't get you the time for it at the moment as query 2 is still running after 5:00 minutes +

  • yeah I think you will be waiting a while, I was going for 10minutes and then my stupid fat thumbs hit the stop button 🙁

    so I'm still waiting 5minutes and counting.

    Try the not exists one as well, the results seem very good, and IO and CPU is better 🙂

    let me know if you find out more... I'm not gonna sleep tonight he he he

    ----------------------------------------------
    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
  • Query finished, ran 9 minutes 14 secods and returned 7203 rows. Some of the estimated rows are quite high, 9 B for one of them.

    I have uploaded my actual execution plan. I'm no expert when it comes to reading these.

  • Here are the statistics io and time results from mine and Chris's fast queries:

    (7203 row(s) affected)

    Table 'Table2'. Scan count 1, logical reads 168, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Table1'. Scan count 2, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 188 ms, elapsed time = 709 ms.

    (7203 row(s) affected)

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Table1'. Scan count 1, logical reads 62, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Table2'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 47 ms, elapsed time = 63 ms.

  • I have an idea on why the difference between SQL Server 2000 and SQL Server 2005.

    One SQL Server 2000, the database engine scanned Table2 once. On SQL Server 2005, the database engine scanned Table2 3 times.

    How do I come to this conlusion, look back at the jpeg picture of the Execution Plan for SQL Server 2000. Compare it to the Execution plan I posted earlier.

    Also, I just reran everything, and will post the execution plans here as well as the statistics io and times.

    --- Using SQL Server 2005 EXCEPT ---

    (7203 row(s) affected)

    Table 'Table2'. Scan count 1, logical reads 168, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Table1'. Scan count 2, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 156 ms, elapsed time = 183 ms.

    --- Using SQL Server 2005 EXCEPT ---

    --- Using NOT EXIST ---

    (7203 row(s) affected)

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Table1'. Scan count 1, logical reads 62, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Table2'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 47 ms, elapsed time = 55 ms.

    --- Using NOT EXIST ---

    --- Using NOT IN with DISTINCT in subquery ---

    (7203 row(s) affected)

    Table 'Table2'. Scan count 3, logical reads 1030031, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Table1'. Scan count 1, logical reads 62, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 355953 ms, elapsed time = 355980 ms.

    --- Using NOT IN with DISTINCT in subquery ---

    --- Using NOT IN with out DISTINCT in subquery ---

    (7203 row(s) affected)

    Table 'Table2'. Scan count 3, logical reads 1030031, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Table1'. Scan count 1, logical reads 62, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 349547 ms, elapsed time = 349514 ms.

    --- Using NOT IN with out DISTINCT in subquery ---

  • Hi Lynn

    Sorry for the late reply.

    Thanks for all the replies

    This is just crazy. I'm in the process of migrating my sql 2000 server to a sql 2005 server but I'm concerend that there are other similar type queries that run fine in 2000 but take forever in 2005.

    I discovered this problem when I migrated a sql 2000 dts package to a 2005 package and saw that the 2 packages have diffenrent execution times.

    I thought I was going crazy, but was glad to see that you guys had the same problems 🙂

    I have over 500 dts packages that I need to migrate so I'm sure you realise my concern with other queries that might also have a performance issue in 2005 but not in 2000

  • Let me throw another monkey into the wrench. Over a year ago we were upgrading our PeopleSoft HR system from version 8.8 to 9.0, and this required an upgrade from SQL Server 2000 to SQL Server 2005. As such, we also upgraded SQL Server for our PeopleSoft Finance system from SQL Server 2000 to SQL Server 2005.

    We had significant performance issues with the Finance system after the upgrade. After weeks of working on it, with the help of PeopleSoft trace we finally identified the problem. There was a table with approximately 200,000 rows and abolutely no indexes what so ever on the table. 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. As soon as we indexed this table, however, all our performance issues vanished.

    Why did everything work fine under SQL Server 2000 with the table unindexed, but caused issues under SQL Server 2005? We don't know, but I'm glad we figured it out and fixed it.

  • Lynn Pettis (4/15/2009)


    Let me throw another monkey into the wrench. Over a year ago we were upgrading our PeopleSoft HR system from version 8.8 to 9.0, and this required an upgrade from SQL Server 2000 to SQL Server 2005. As such, we also upgraded SQL Server for our PeopleSoft Finance system from SQL Server 2000 to SQL Server 2005.

    We had significant performance issues with the Finance system after the upgrade. After weeks of working on it, with the help of PeopleSoft trace we finally identified the problem. There was a table with approximately 200,000 rows and abolutely no indexes what so ever on the table. 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. As soon as we indexed this table, however, all our performance issues vanished.

    Why did everything work fine under SQL Server 2000 with the table unindexed, but caused issues under SQL Server 2005? We don't know, but I'm glad we figured it out and fixed it.

    Thanks for the extra monkey in the wrench 🙂

    So should every table in the db then be indexed, to be safe?

    Are these issues not addressed in 2008?

  • I've seen a few posts like this where the odd query or three gets messed up by the upgrade, but I've never seen any large number of procedures get messed up by any of the upgrades I've done to 2005 or 2008.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi Lynn

    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

    I'm not sure what you mean by this statement??

    Do you mind explaining what this all means. Please excuse the ignorance

Viewing 15 posts - 46 through 60 (of 68 total)

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