April 15, 2009 at 10:04 am
Rowan (4/15/2009)
Thanks Lynn, i will try that asapI 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?
April 15, 2009 at 10:05 am
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]
April 15, 2009 at 10:06 am
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]
April 15, 2009 at 10:11 am
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?
April 15, 2009 at 10:23 am
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]
April 15, 2009 at 10:30 am
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 +
April 15, 2009 at 10:32 am
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]
April 15, 2009 at 10:39 am
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.
April 15, 2009 at 10:46 am
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.
April 15, 2009 at 11:24 am
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 ---
April 15, 2009 at 3:35 pm
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
April 15, 2009 at 4:07 pm
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.
April 15, 2009 at 5:09 pm
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?
April 15, 2009 at 5:15 pm
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
April 15, 2009 at 5:27 pm
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