Speed up table update

Question

Speed up table update

gary.morey

SSC Eights!

Points: 817
More actions
May 18, 2011 at 7:24 am

#100378

I am trying to run the following update query:
UPDATE WebSessionsPerCall
SET WebSessionsPerCall.CALL_ID = NumberOfWebSessionsBeforeCall.CALL_ID
FROM NumberOfWebSessionsBeforeCall
WHERE NumberOfWebSessionsBeforeCall.USERID = WebSessionsPerCall.USERID
AND NumberOfWebSessionsBeforeCall.OPEN_TIME = WebSessionsPerCall.OPEN_TIME
GO
The WebSessionsPerCall table has about 89 million rows in it. The NumberOfWebSessionsBeforeCall table has about 460K rows in it. The WebSessionsPerCall table has no indexes. The NumberOfWebSessionsBeforeCall table has a primary key index on the CALL_ID.
The query has been running for 17 hours. Do you have any suggestions on how to speed up the update?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

gary.morey (5/18/2011)
I am trying to run the following update query:
UPDATE WebSessionsPerCall
SET WebSessionsPerCall.CALL_ID = NumberOfWebSessionsBeforeCall.CALL_ID
FROM NumberOfWebSessionsBeforeCall
WHERE NumberOfWebSessionsBeforeCall.USERID = WebSessionsPerCall.USERID
AND NumberOfWebSessionsBeforeCall.OPEN_TIME = WebSessionsPerCall.OPEN_TIME
GO
The WebSessionsPerCall table has about 89 million rows in it. The NumberOfWebSessionsBeforeCall table has about 460K rows in it. The WebSessionsPerCall table has no indexes. The NumberOfWebSessionsBeforeCall table has a primary key index on the CALL_ID.
The query has been running for 17 hours. Do you have any suggestions on how to speed up the update?

What other Indexes do you have that are used in the JOINS and THE WHERE Clause?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

gary.morey SSC Eights! Points: 817 More actions · Answer 2

There are no other indexes. I was under the impression that more indexes would actually adversely impact performance. Is this not the case?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

gary.morey (5/18/2011)
There are no other indexes. I was under the impression that more indexes would actually adversely impact performance. Is this not the case?

More indexes will will slow down write performance but increase read performance.

You should have a PK and Clustered Index on both of these tables.

You should have an Index to Support JOINS's (FK Column).

You should have a non-clustered Index on Columns in the Where Clause unless they are not highly selective.

Consider covering indexes as well when appropriate.

You might want to take a look at you execution plan.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Please post the estimated execution plan... or the actual plan from the cache.

Looks like you have an hidden triangular join but I'm not 100% sure.

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 5

Is this for data export? You don't have any kind of where clause at all. You're likely to see nothing but table scans from a query like that, and based on your data sizes, that's going to be painful. If this is not an export, why isn't the data filtered in some fashion?

"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

gary.morey SSC Eights! Points: 817 More actions · Answer 6

I broke the export into batches which solved the problem.

Thank you.