December 19, 2008 at 4:11 am
Hey all,
I have a scenario where I have a table containing lots of data which needs to be processed by a SPROC, when I say lot's I mean it could contain circa 3-6million rows. This data needs to be transformed into fact tables for analysis services to process. The script is executed as a job every 10 minutes.
After a lot of tweaking I have the script performing extremely well, the biggest problem I have is deleting of the data which I have processed in the source tables at the end of the sproc. Initially I played around using DELETE and have tried many techniques after researching on this forum but at the end of the day they still aren't as quick as I would like.
In my current test scenario I have ended up performing the following:
DECLARE @Seed BIGINT = (SELECT MAX(Id) FROM SrcTable)
IF @Seed IS NULL SET @Seed = 1 ELSE SET @Seed = @Seed + 1
TRUNCATE TABLE SrcTable
DBCC CHECKIDENT('SrcTable', RESEED, @Seed)
As you would expect this is very very fast - 6 million rows in 0.063s 🙂 Now my problem is that as I am processing the script additional rows are coming into the table. At the top of my script I select the rows I wish to process into a temporary table and work with that. Then I need to delete those rows from the source table when I am finished.
Using the truncate method I obviously have to ensure that no rows are inserted while the script runs. I have been toying around with the idea of using an application lock as a mutex as I only have one other process which inserts into the table but for some reason this doesn't feel like a good move. I don't like the idea of the two processes deadlocking for inserts/script processing.
However when I go down the path of using deletes the deleting takes longer than the entire script which is doing a large volume of work but for 6 million rows I have the entire cycle running around 2.10 mins and this is updating a bunch of dimension and fact tables.
As I say I have researched these forums for ideas such as batching deletes, and I only have the clustered PK index so there's no issue there, and I am currently in simple recovery mode although I aren't confident about the risk of using this in production. I am also not happy about turning off logging due to the fact inserts are taking place concurrently.
I hope that makes sense, if anybody has any ideas then thanks in advance.
Kind Regards,
Mark
December 19, 2008 at 4:26 am
Oh one more thing....
Instead of perhaps an app_lock mutex am I able to somehow lock the table while I perform the seed and truncate operations? They are very fast so I would hope not to cause deadlocks with inserts? Insert deadlocks would be very bad in my scenario.
Cheers,
Mark
December 19, 2008 at 5:27 am
I would suggest that you specify the query HINT NOWAIT with your DELETE statement as follows
DELETE WITH (NOWAIT)
December 19, 2008 at 6:03 am
Hi,
Thanks for the reply, I tried this with 6million rows and I stopped the query once it hit 8 minutes and I have a quad core processor.
Admittedly I did not have time to try this in a blocked loop of deletes but I imagine it's not going to help too much.
That's just not quick enough for me. Am I trying to achieve the impossible here? There must be a nicer solution.
Regards,
Mark
December 19, 2008 at 12:06 pm
If you can use truncate then it will always be quicker as it doesn't have to delete (and log) each table individually - the price is that it is not a fully logged operation. If you are stuck with deletes then batching them is the way to go. If truncating, then the process takes a table lock so any other process attempting to insert will be blocked for the duration.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply