February 25, 2008 at 7:40 am
Hello,
I have very funnt problem:
Following select runs on SQL 2000 within 3 secs (on SQL 2005 takes more than 6 mins)
SELECT HWID1, HWID2, Status FROM REF_UPD_HWID
WHERE HWID1 NOT IN (SELECT HWID1 FROM HWID)
REF_UPD_HWID table definition:
CREATE TABLE REF_UPD_HWID (
[HWID1] [int],
[HWID2] [varchar] (50),
[status] [char] (1)
) ON [PRIMARY]
When I've changed in above definition [HWID1] [int] not null. Query was much faster and lasted about 6 secs on SQL 2005. But this solution has some implcations and can't be implemented.
Any ideas?
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
February 25, 2008 at 8:04 am
Have you updated stats/rebuilt indexes after migrating the db to 2005?
If you look at the execution plan for both, what are the differences?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2008 at 8:15 am
Was this upgraded? (hence Gail's question) or was it rebuilt on 2005 from scratch and has bad performance? Are the data sets the same?
February 26, 2008 at 12:51 am
It wasn't upgrade. I just set up second SQL 2005 instance and restore this DB there.
In Execution plan there is only one difference. In SQL 2000 there is in incex scan i SQL 2005 table scan. It seems that by adding this magic NOT NULL to table definition SQL 2005 moved to Index scan. I will give you update soon with two plans pics.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
February 26, 2008 at 1:28 am
If you restored a 2000 database onto 2005, you need to update all the statistics. SQL 2005 keeps more detailed stats than SQL 2000 did. The 2005 optimiser can use the older format of stats, but not that efficiently.
UPDATE STATISTICS <Table Name>
If that doesn't resolve the issue, can you list the indexes you have on the table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 26, 2008 at 4:42 am
Sorry I made an mistake. This table is created in the first step of SSIS Package.
Then in second step I copy data from production to this table. And then runs this query which I mentioned.
Some Statistics:
SQL 2000:
Number of rows in REF_UPD_HWID: 879 222
Number of rows returned by subquery (807 717)
Number of returned rows: 71 508
Last Execution SQL 2000: 3 Sec
http://decktech.eu/ebay/sql/SQL2000EP.JPG
http://decktech.eu/ebay/sql/sql2000stats.JPG
http://decktech.eu/ebay/sql/sql2000TR.JPG
SQL 2005
Number of rows in REF_UPD_HWID: 879 257
Number of rows returned by subquery (878 572)
Number of returned rows: 1 106
http://decktech.eu/ebay/sql/SQL2005EP1.JPG
http://decktech.eu/ebay/sql/sql2005ep2.JPG
Last Execution SQL 2005: 25 mins 19 sec
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
February 26, 2008 at 5:01 am
- Can you post the ddl (sql2000 and sql2005) ? (tables/indexes/views)
- What's the reason the result sets are different ?
- Can you rewrite the query to a :
SELECT HWID1, HWID2, Status
FROM REF_UPD_HWID R
WHERE NOT exists (SELECT 1 FROM HWID x where x.HWID1=R.HWID1)
- is any of the tables pinned in sql2000 ?
btw you can attach files at the bottom of the reply window (edit attatchment button) 😉
You can also post the xmlplan from sql2005
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 26, 2008 at 6:56 am
ALZDBA (2/26/2008)
- Can you post the ddl (sql2000 and sql2005) ? (tables/indexes/views)
CREATE TABLE REF_UPD_HWID (
[HWID1] [int],
[HWID2] [varchar] (50),
[status] [char] (1)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[HWID](
[HWID1] [int] IDENTITY(10000,1) NOT NULL,
[HWID2] [varchar](50) NOT NULL,
[status] [char](1) NOT NULL,
CONSTRAINT [PK_HWID] PRIMARY KEY NONCLUSTERED
(
[HWID1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
ALZDBA (2/26/2008)
- What's the reason the result sets are different ?
HWID is defferent on both instances. I migrated database from SQL 2000 to SQL 2005 using side by side scenario. So SQL 2005 HWID is a little bit different now.
ALZDBA (2/26/2008)
- Can you rewrite the query to a :
SELECT HWID1, HWID2, Status
FROM REF_UPD_HWID R
WHERE NOT exists (SELECT 1 FROM HWID x where x.HWID1=R.HWID1)
Amazing now query is faster than in SQL 2000. (Duration 0 sec)
Your'e genius !
ALZDBA (2/26/2008)
- is any of the tables pinned in sql2000 ?
No
ALZDBA (2/26/2008)
btw you can attach files at the bottom of the reply window (edit attatchment button) 😉
You can also post the xmlplan from sql2005
Done 🙂 Any explanation why it helped so much?
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
February 27, 2008 at 4:42 pm
You could probably get the same or better performance from:
SELECT R.HWID1, R.HWID2, R.Status
FROM REF_UPD_HWID R
LEFT JOIN HWID H ON
R.HWID1 = H.HWID1
WHERE
H.HWID1 IS NULL
I've found using this type of left join on NULL to be very fast in both SQL 2000 and 2005.
Todd Fifield
February 28, 2008 at 8:51 am
I recently had to do something similar and I have always used NOT EXISTS correlated subqueries. I experimented with several methods (sub query, outer join, in ()) and found something like this to work best (lowest cost and fewest rows).
;WITH newcodes as
( SELECT HWID1
FROM REF_UPD_HWID
EXCEPT
SELECT HWID1
FROM HWID )
SELECT r.HWID1, r.HWID2, r.Status
FROM REF_UPD_HWID r
JOIN newcodes x ON r.HWID1 = x.HWID1
February 28, 2008 at 9:21 am
in many cases the [not] exists with a correlated query will outperform a
left join with the where rightpart-table-column is null because
a [not] exists is performed using the (fast) keyvalues, where
the left join will need to be completed before the is null expression is evaluated.
Test it for your case and choose the best performing one :w00t:
Your query will probably be even faster if you add an index to the used column(s) !
(REF_UPD_HWID column HWID1)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 28, 2008 at 12:57 pm
Thanks for the post on NOT EXISTS. I'm going to try it out on large record sets to see what happens. I've benchmarked the LEFT JOIN WHERE right-table IS NULL against a NOT IN (SELECT...) type sub query in the WHERE clause and the LEFT JOIN always out performed the NOT IN.
I'm going to try the NOT EXISTS type of sub query.
Todd Fifield
March 3, 2008 at 10:40 am
ALZDBA (2/28/2008)
in many cases the [not] exists with a correlated query will outperform aleft join with the where rightpart-table-column is null because
a [not] exists is performed using the (fast) keyvalues, where
the left join will need to be completed before the is null expression is evaluated.
Absolute correct ! I compared those 4 versions and here you can see results:
SELECT HWID1, HWID2, Status FROM Qua_WCSA.REF_UPD_HWID R WHERE NOT exists (SELECT 1 FROM HWID x where x.HWID1=R.HWID1)
"SELECT R.HWID1, R.HWID2, R.Status
FROM REF_UPD_HWID R
LEFT JOIN HWID H ON
R.HWID1 = H.HWID1
WHERE
H.HWID1 IS NULL"
Both very good results but slightly better first query, so not exists beats left join 🙂
CPU MinCPU MaxCPU avgReadsDuration minDuration maxDuration avg
1627186117446077481560520.5
179619691882.56077509575542
"SELECT r.HWID1, r.HWID2, r.Status
FROM REF_UPD_HWID r
JOIN HWID x ON r.HWID1 = x.HWID1 "
2346273425406077108651181411339.5
Very long duration, CPU and reads almost ok.
"SELECT HWID1, HWID2, Status
FROM REF_UPD_HWID
EXCEPT
SELECT HWID1,HWID2, Status
FROM HWID"
487249554913.521281138414351409.5
Almost doubled CPU and ridiculous reads, but duration much better than third one.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
March 3, 2008 at 1:16 pm
Berto,
I'd like to know how the (3 actually including the EXCEPT) version perform on large tables where a significant number of rows are returned.
Todd Fifield
March 4, 2008 at 12:57 am
HWID is quite big 886 927 rows. Subquery returns about 300-1000.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply
