November 9, 2006 at 5:42 am
We have a regular batch process that loads data (300,000 - 500,000 rows at a time) into a holding table, cleans it and then inserts it into a partitioned view (inorder to distribute the data into monthly history tables based on a date field)
At the start of each execution of the batch, the holding table is truncated, rather than deleted, since this is much quicker than a delete and impacts logging far less.
The holding table contains an IDENTITY column which acts as a PK. Because we are truncating the table, the IDENTITY value would be reset to 0 for each run of the batch if we didn't re-seed it. The processing looks like this, then:
1. Start batch
2. Get max(ID_value) from table and save in a variable
3. Truncate table
4. DBCC CHECKIDENT table, RESEED with (previous_ID + 1)
5. Insert new data
I've noticed from running Profiler traces that the DBCC CHECKIDENT command alone is taking anything between 45 and 90 seconds to complete.
Can anyone think why this might be?
November 9, 2006 at 5:49 am
it might not be aware ( internally ) of the changes - try an update statistics on the table first. or a dbcc updateusage ( although that may take time )
I'm told that the dbcc checkident isn't 100% guaranteed , although I've tested it extensively and use it. Failing that you could truncate the table, drop it and recreate it with the correct seed in a dynamic script ??
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply