How to tune this sql statement? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to tune this sql statement?

I have a very simple T-SQL statement which takes hours to run. The statement is something like: update table1 set column1 = coalesce(table2.column1, ”),
column2 = table2.column2, column3 = table2.column3
from table1, table2
where table1.column4 = table2.column4 The machine is a P3 800 512 ram and 80G HD with 8M cache. I’m running Windows 2000 Server and SQL Server 2000. Table1 has about 500,000 records and Table2 has about 2 millions records. I have added non-clustered indexes for table1.column4 and table2.column4. When I check Execution Plan, it has Hash Match/Aggregate which costs about 50%.
Thank you for any help.
What else show execution plan?. Indexs are used in execution plan?. What cost each one?.
Did you try Index Tuning Wizard in SQL Analyzer?
What level of Services Pack have you installed on OS and SQL?
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Try to break it into batches. Set the rowconnt to something smaller like 10,000. add a condition to the where clause to make sure you are not updating the same rows (where column4 > x) and run it in a loop as long as you reach a certain value of x or as long as @@rowcount is 0.
Hi Luis, I find most of the hardware/software information provided in the original post is not correct. Here is the updated information: Operating System: Microsoft Windows NT – 5.0(2195)
Product Version: 8.00.760 (SP3)
OS memory: 256(MB)
Hard Drive: 12.6 GB update table1 set column1 = coalesce(table2.column1, ”),
column2 = table2.column2, column3 = table2.column3
from table1, table2
where table1.column4 = table2.column4 Table1: 1343488 records
Table2: 524288 records It took about 7 hrs to run the T-SQL statement. Does the Estimated Execution Plan provide some hint how long the T-SQL statement will run? This is the Estimated Execution Plan from SQL Query Analyzer: Update
Cost: 0% Physical operation: UPDATE
Logical operation: UPDATE
Estimated row count: 1148114
Estimated subtree cost: 307 Table Update/Update
Cost: 0% Physical operation: Table Update
Logical operation: Update
Estimated row count: 1148114
Estimated row size: 21
Estimated I/O cost: 0.0103
Estimated CPU cost: 1.15
Estimated number of executes: 1.0
Estimated cost: 1.158450(0%)
Estimated subtree cost: 307 Compute Scalar
Cost: 0% Physical operation: Compute Scalar
Logical operation: Compute Scalar
Estimated row count: 1148114
Estimated row size: 63
Estimated I/O cost: 0.000000
Estimated CPU cost: 0.114
Estimated number of executes: 1.0
Estimated cost: 0.114800(0%)
Estimated subtree cost: 306 Top
Cost: 0% Physical operation: Top
Logical operation: Top
Estimated row count: 1148114
Estimated row size: 63
Estimated I/O cost: 0.000000
Estimated CPU cost: 0.114
Estimated number of executes: 1.0
Estimated cost: 0.114800(0%)
Estimated subtree cost: 305 Hash Match/Aggregate
Cost: 55% Physical operation: Hash Match
Logical operation: Aggregate
Estimated row count: 1148114
Estimated row size: 63
Estimated I/O cost: 106
Estimated CPU cost: 64.0
Estimated number of executes: 1.0
Estimated cost: 169.770770(55%)
Estimated subtree cost: 305 Hash Match/Inner Join
Cost: 55% Physical operation: Hash Match
Logical operation: Inner Join
Estimated row count: 1148114
Estimated row size: 83
Estimated I/O cost: 27.7
Estimated CPU cost: 38.1
Estimated number of executes: 1.0
Estimated cost: 65.797972(21%)
Estimated subtree cost: 136 Table Scan
Cost: 21% Physical operation: Table Scan
Logical operation: Table Scan
Estimated row count: 524288
Estimated row size: 73
Estimated I/O cost: 64.8
Estimated CPU cost: 0.576
Estimated number of executes: 1.0
Estimated cost: 65.341042(21%)
Estimated subtree cost: 65.3 Index Scan
Cost: 1% Physical operation: Index Scan
Logical operation: Index Scan
Estimated row count: 1343488
Estimated row size: 25
Estimated I/O cost: 2.99
Estimated CPU cost: 1.48
Estimated number of executes: 1.0
Estimated cost: 4.468086(1%)
Estimated subtree cost: 4.47 When I run the script, I check the Process Info and see status: sleeping and wait type: pageiolatch. I plan to run the script on a better machine to see how long it takes:
P4 2.60 GHz
768MB of RAM
Windows XP Professional SP1
SQL Server 2000 SP3
Hard Drive 1: 30GB 2MB Buffer (tempdb)
Hard Drive 2: 80GB 8MB Buffer (my database)
Thank you for any suggestion.
pcsql, did you try what I suggested on SQLTeam.com? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
I would suggest using ISNULL against COALESCE as in some of my previous tests, I have seen it perforaming better. Secondly, I would recommend creating non clustered index on all the columns used in the query in table2. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Hi Everyone, I use the Index Tuning Wizard (suggested by Luis) and add a nonclustered index for all the columns in the statement (exactly what gaurav_bindlish has suggested). The index contains Column1, Column2, Column3, Column4.
With this index, the statement took about 15 minutes or less. The Estimated Execution Plan: Update – Cost 0%
Table Update/Update – Cost 1%
Compute Scalar – Cost 0%
Top – Cost 0%
Stream Aggregate/Aggregate – Cost 5%
Nested Loops/Inner Join – Cost 3%
Index Scan (table1) – Cost 3%
Index Seek (table2) – Cost 88% Can someone explain to me why this index will speed the performance? Well, I believe I find the answer for this question: Covering index. Now, I’m working on few other statements which have similar problem but the record sets are even larger. So, I may still need to try: batches – suggested by bambola
inner loop join – suggested by derrickleggett in dbforums
isnull instead of coalesce – suggested by gaurav_bindlish
Thank you for any help.
Yes the answer is covering index. Baching should be of good help as this relives buffer pool of many pages which have been committed in the previous batch and the transaction log pages which have been committed. Having a checkpoint between the batches will also help. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
]]>