September 17, 2010 at 7:37 am
I have the following query, which does an update statement but everytime I run it it took about 1hr and 20 mins to run so I created Indexes because that would solve the problem. After creating the Indexes I re-run the query but it was the same so I checked to see if it was blocked by running the following query and under the spid it had the same spid in under the blocked column, the lastwaittype is PAGEIOLATCH_SH.
UPDATE table
SET column1 = column2
FROM table1 JOIN table2
ON column1= column2
Thank you
September 17, 2010 at 7:49 am
can you post the actual query that you ran, as well as the table structres and actual execution plan?
September 17, 2010 at 8:45 am
Also, a row count would be nice. If you've got a wide, long table, that could take a long time no matter how well optimized your query is.
September 17, 2010 at 8:53 am
If you don't need this in a single transaction, you can try updating batches of rows. Sometimes that is quicker than one large update.
September 17, 2010 at 9:04 am
tt-615680 (9/17/2010)
I have the following query, which does an update statement but everytime I run it it took about 1hr and 20 mins to run so I created Indexes because that would solve the problem. After creating the Indexes I re-run the query but it was the same so I checked to see if it was blocked by running the following query and under the spid it had the same spid in under the blocked column, the lastwaittype is PAGEIOLATCH_SH.UPDATE table
SET column1 = column2
FROM table1 JOIN table2
ON column1= column2
Thank you
If the query really does look like this, then it's an unrestricted update. I'm sure it's a typo on your part and it looks like this:
UPDATE t1 SET column1 = t2.column2
FROM table1 t1
INNER JOIN table2 t2
ON t2.column1 = t1.column2
Post it up and we'll see.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 17, 2010 at 9:09 am
Update statistics... Check if have outdated ststistics.
No matter how good indexes you have on your tables, if your statistics are outdated, then Query optmizer can't generate optimal execution plans.
Also check if you have any Key, RID lookups, Table scans or Index scans in your exec plan.
If so try to create appropriate indexes. Also please check if the existing indexes are very fragmented.
BTW what version of SQL Server are you using?
Thank You,
Best Regards,
SQLBuddy
September 17, 2010 at 9:17 am
Also PAGEIOLATCH_SH indicates a possible I/O bottleneck on the server. Often mmeory pressures lead to inreased I/O.
Please check if you are having any memory pressures...
Check the following counters..
1. Buffer Cache Hit Ratio
2. Page Life Expectancy
3. Checkpoint Pages\Sec
4. Lazywrites\Sec
If possible try to post the Query Execution plan.
Thank you,
Best Regards,
SQLBuddy
September 17, 2010 at 9:26 am
Chris Morris-439714 (9/17/2010)
tt-615680 (9/17/2010)
I have the following query, which does an update statement but everytime I run it it took about 1hr and 20 mins to run so I created Indexes because that would solve the problem. After creating the Indexes I re-run the query but it was the same so I checked to see if it was blocked by running the following query and under the spid it had the same spid in under the blocked column, the lastwaittype is PAGEIOLATCH_SH.UPDATE table
SET column1 = column2
FROM table1 JOIN table2
ON column1= column2
Thank you
If the query really does look like this, then it's an unrestricted update. I'm sure it's a typo on your part and it looks like this:
UPDATE t1 SET column1 = t2.column2
FROM table1 t1
INNER JOIN table2 t2
ON t2.column1 = t1.column2
Post it up and we'll see.
It should be
UPDATE table2
SET table2.column1 = table1.column2
FROM table1 JOIN table2
ON column3= column4
Thank you
September 17, 2010 at 9:39 am
Those are interesting table names.
Please post the actual update statement... sometimes there are parts of it you don't suspect that actually cause the issue.
Generifying the query doesn't help us help you.
September 17, 2010 at 9:40 am
tt-615680 (9/17/2010)
Chris Morris-439714 (9/17/2010)
tt-615680 (9/17/2010)
I have the following query, which does an update statement but everytime I run it it took about 1hr and 20 mins to run so I created Indexes because that would solve the problem. After creating the Indexes I re-run the query but it was the same so I checked to see if it was blocked by running the following query and under the spid it had the same spid in under the blocked column, the lastwaittype is PAGEIOLATCH_SH.UPDATE table
SET column1 = column2
FROM table1 JOIN table2
ON column1= column2
Thank you
If the query really does look like this, then it's an unrestricted update. I'm sure it's a typo on your part and it looks like this:
UPDATE t1 SET column1 = t2.column2
FROM table1 t1
INNER JOIN table2 t2
ON t2.column1 = t1.column2
Post it up and we'll see.
It should be
UPDATE table2
SET table2.column1 = table1.column2
FROM table1 JOIN table2
ON column3= column4
Thank you
The table placement may well be the source of your performance problem.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 17, 2010 at 9:44 am
Chris Morris-439714 (9/17/2010)
The table placement may well be the source of your performance problem.
I have heard this before, that the order of the table placement in the join affects the update statement but have never seen any proof / references for this.
September 17, 2010 at 11:37 am
steveb. (9/17/2010)
Chris Morris-439714 (9/17/2010)
The table placement may well be the source of your performance problem.
I have heard this before, that the order of the table placement in the join affects the update statement but have never seen any proof / references for this.
The only time it's affected me was when I had inner joins, outer joins, more inner joins but joined to the outer joins, and "lost" records in my result set. I've never had it cause a performance issue though.
But I think Chris hit the nail on the head. If the generic query is a direct translation and Column1 and Column2 in both parts of the query *are* the same columns, that's the problem right there. Until the OP posts table structures, sample data, and the actual query, though, we're all just posting into the wind.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply