Best way to run update for million records

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)

Best way to run update for million records

Author

Topic

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-09-18 : 09:14:31

Hi guys,

I've to update more than 10 lac of records, i created a script but it took more than 24 hrs in test database.. How to go about that?

Pasting the sample script:

all The tables used in the query below contains more than 5 lacs of records.. couple of them have more than 10 lacs of records

:::Script :::
IF Object_id('tempdb..#temp_test') IS NOT NULL
DROP TABLE #temp_test

SELECT eid, alarmCode1
INTO temp_test
FROM Test_Data
WHERE type_data <> 1
AND eid IN (
SELECT eh.uid
FROM loggedincomingmessage lim, loggedsession ls, eventheader eh, asset a
WHERE lim.sessionuid = ls.sessionuid
AND ls.starttime = eh.calltime
AND eh.companyuid = @v_companyUid
AND ls.modulemsn = a.modulemsn
AND a.uid = eh.assetuid
AND eh.eventtime BETWEEN '2009-08-20 00:00:00.000' AND GETDATE()
AND SUBSTRING(lim.message,1,2) = XXXX)

SELECT * from temp_test

WHILE EXISTS (SELECT *
FROM temp_test)
BEGIN

SELECT TOP 1 @eid = eid
, @alarmCode1 = alarmCode1
FROM temp_test
ORDER BY eid

SELECT @eid
, @alarmCode1

UPDATE Test_Data
SET alarmtype = @alarmCode1,
alarmCode1 = NULL
WHERE eid = @eid

DELETE temp_test
WHERE eid = @eid

END

SELECT eid, alarmCode1
FROM Test_Data
WHERE type_data <> 1
AND eid IN (
SELECT eh.uid
FROM loggedincomingmessage lim, loggedsession ls, eventheader eh, asset a
WHERE lim.sessionuid = ls.sessionuid
AND ls.starttime = eh.calltime
AND eh.companyuid = @v_companyUid
AND ls.modulemsn = a.modulemsn
AND a.uid = eh.assetuid
AND eh.eventtime BETWEEN '2009-08-20 00:00:00.000' AND GETDATE()
AND SUBSTRING(lim.message,1,2) = XXXX)

please help..

Thanks,
Sourav

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-09-18 : 11:38:57

You are updating the records one at a time. Stop doing that immediately!

It's not clear to me what you are trying to accomplish. Your code seems to pull a record out of the Test_Data table and then update the Test_Data table with that record. Can you clarify?

Oh, and what the heck are "lacs"?

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-18 : 11:46:01

First, you should get familiar with ANSI style joins. It is hard to tell what your IN statement is doing when you use the old, deprecated, join syntax. Secondly, you should try doing your update in a set instead of singleton updates. SQL is going to perform much, much better on a set.

One that that might help speed things up is to change the SUBSTRING to a LIKE clause. If you have an index on that Message column SQL can take advantage of it with a LIKE. Also, you might try getting rid of the IN clause and replace it with joins.

If you need more help, I think we'd need sample data, expected output and how the tables are indexed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources