Bulk delete/update

[pvuppa1]

Can someone tell me how do I delete some rows effectively on a daily basis and update it with new rows from a temporary sql table.

I have a huge table of 120 Mil rows(actualtable). and I need to update it with 1mil rows(temptable) everyday.

I usually do this but it takes awful lot of time and I guess there might be a better way to accomplish the following:

delete * from actualtable where "somecondition"
insert * into actualtable from temptable

Wanted to use bcp, bulkinsert but nothing helps.

Both are sql tables and im using sql svr 2k.

Thx in advance.

-P

 

[yksvaan]

I think this is kind of optimation issue. So you would get the normal q's and a's about your situation, like splitting table, not having too many indexes, dropping indexes before load and recreating after load, and so on. You probably find these topics covered on sql administration forum.

I'll just point out one here, which is actually not an optimation issue. Is the table reserved for the load window, so there's no application/user activity? If it is, use tablelocks on it. That reduces some unnecessary overhead (some times a lot).

DELETE FROM ACTUALTABLE WITH (TABLOCKX) WHERE "condition"

INSERT INTO ACTUALTABLE WITH (TABLOCKX)
SELECT * FROM tembtable WITH (TABLOCK) -- not so critical

And rather have these on different batches, like two different SQL Task, if you are using dts package.

And of course, no transaction handling like BEGIN TRANSACTION. Which remainded me, there's a Use transactions-option in Package properties and you can also select dts-steps to be part of the transaction. Clear Use transactions, in my opinion it's normally only unneeded overhead. Transactions should only be used in carefully desingned situations, not just anywhere.

BTW, do you need at all transaction log backups, is this a datawarehouse? If it is (in my opinion in most cases then you don't need transaction log bakcups), change your db's Recovery model to Simple, and start taking only full backups. In this particular case this may not be a sensible advice, since the db might be so huge, but consider this possibility too.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]