Transactions in stored procedures
-
Dude you're on the highway at 100 MPH on the wrong side of the highway with incomming traffic. Traffic may be light when you do this but it's still not a good idea.
-
On a system where there is no other activity, Select Into should work with no problems.
I have used it myself several times, and have seen it used plenty of times, and it has made a HUGE difference to performance, with no locking problems at all.
-
Same thing with russian roullete, many clicks can come before the bang. Anyways I can't stop you guys from doing that so good luck.
-
Am I missing something here?
On a system with no other users, who are you going to block?
Deleting 1.5 million rows from a table in a transaction is going to block anybody wanting to use that table anyway.
It doesn't sound as though this work is being done in tempdb, as he's trying to 'rebuild' a table in a user database, so the worst you can do is stop anybody updating sysobjects, syscolumns, sysindexes etc in the user database.
-
You're right. I'm reffering to the select into part.
You never know when this task will need to be done in the day. That's why I'd stay away from it.
-
It may need running during the day if it fails at night.
Any other ideas as to why my current approach struggles to work using a Transaction?
There are 3 parts to this load: Create primary keys, delete records, insert records.
As I understand it, without running inside a transaction SQL will commit those parts as each finishes. However, when running inside a transaction it will wait until the last one completes and then commit.
So, as the load works without using a transaction, is the reason for failure simply that SQL can't keep that amount of info in memory/updated in the log before the commit? Could it be a resource issue in terms of RAM on the server or hard drive space?
-
As an alternatve solution tu the truncate table method you could also copy the "good" records to a new table (not temp table) drop the original table, then rename the newly created table to the original name.
This is the way you can save the most of the log space because you are inserting just once (instead of twice in case of insert-truncate-insert).
The insert will be even faster because no index exists yet on the new table, what you will have to create after you have renamed it.
Bye
Gabor -
and if the number of "good" records is large move them into the live table a chunck at a time and take transaction logs backup more frequently to let it circle around
* Noel
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply