Select Query Fast, Update Query Slow...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Query Fast, Update Query Slow...

Author

Topic

Humate
Posting Yak Master

101 Posts

Posted - 2010-01-13 : 15:56:17

Hi All,

I am wondering if anyone can provide some help on a query I have written to update my data.

When I run the select part of my query, it completes in around 10 seconds on 417,000 rows of data. However, If I try to run this as part of an update statement, it takes far too long.

UPDATE calllist
SET calllist.ac_uid =

(SELECT AC_UIDUPDATE FROM(

SELECT calllist.cl_id, calllist.calllistinboundid, CASE WHEN e.ac_uid IS NULL THEN e.defaultcampaign COLLATE Latin1_General_CI_AI ELSE e.ac_uid END AS AC_UIDUPDATE
FROM calllist
LEFT OUTER JOIN
(SELECT * FROM
(SELECT calllist.calllistinboundid, calllist.cl_id, calllist.starttime, huntgroups.*, campaign.ac_uid, campaign.brand_uid, ROW_NUMBER() OVER(PARTITION BY calllist.calllistinboundid ORDER BY campaign.datefrom, calllist.cl_id) AS counter
FROM callList
LEFT OUTER JOIN db2.dbo.campaign ON calllist.ddidigits = campaign.number
COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN huntgroups ON calllist.firstrang = huntgroups.code
WHERE calllist.starttime >= campaign.datefrom and calllist.starttime < campaign.dateto) AS d
WHERE d.counter =1) as e
ON calllist.calllistinboundid = e.calllistinboundid

) as G
WHERE G.cl_id = calllist.cl_id)

The update, when limited to 10 records, took 9 seconds. I haven't been able to get it to complete for the full dataset of 417,000 records. I know the query isn't great, but I'm lost as to why the update is much more time consuming than the select.

Any help appreciated

Thanks
Humate

quote:
Originally posted by Michael Valentine Jones

It takes real skill to produce something good out of a giant mess.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-13 : 16:58:46

It's probably to do with the indexes on the calllist table. Indexes have to be kept up to date on all DML operations.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Humate
Posting Yak Master

101 Posts

Posted - 2010-01-13 : 17:25:48

Thanks Tara. I did create indexes on cl_id and calllistinboundid. Just managed to update 100k records, but it took 1 hour 40 mins. I'd like to run this update very quickly every day, and it isn't possible with what I have right now. Is there anything else i'm over looking?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-13 : 18:12:46

You could try dropping your indexes before the update and then adding them back in after the update. You should compare the execution plans for both the update and the select though. You are probably encountering a bad plan for the update due to the query structure.

If you need to update that many rows every day, then you should reconsider your system design.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Humate
Posting Yak Master

101 Posts

Posted - 2010-01-13 : 18:21:39

I do agree, the system design is a problem, not one quickly solved in my situation however.

It is slightly quicker with the indexes in place, although not quick enough to run on a daily basis. I'll try to find a way to query the data better next.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources