CODEWITHSUNDEEP
sqlsql-serverperformancesql-server-2008truncate
mohan
mohan

Reputation: 41

SQL Server - insert/select slow after TRUNCATE TABLE has 6 over million rows

Seeing slowness in insert/select after truncating a table that has over 6 million rows.

I daily insert 5 to 6 millions records into a table and I was able to insert/select data without any issue for some 7 or 8 days but when the table size went above 10 GB / 30 million rows, there were a few timeout issues.

So I thought of truncating the table daily before data uploading since 1 day's data is enough for me. Now I am seeing extreme slowness in insert/select till i rebuild index in middle of upload. last 2 days it took 5 hours to insert 1 million rows , after a index rebuild remaining 3.5 to 4 million rows went into table in less than 15 mins. I dont prefer to rebuild index in middle of my upload process.

i don't do a .NET Bulk insert , i insert rows in batches using Stored proc , since i do some validation.

I am using SQL Server 2008.

Upvotes: 2

Views: 3174

Answers (3)

mohan
mohan

Reputation: 41

After enabling auto stats update , every thing went normal. from now After truncating big tables i will makes sure table stats get updated if DB auto stats update is disabled.. thanks every one for all your valuable suggestions.

Upvotes: 1

Surendra
Surendra

Reputation: 721

I suggest you to drop indexes as well, when you truncated the table and once the data is loaded into the table then go ahead and create the indexes, this will at least solve the issue to some extent.

With the given information I am not sure of what utility you are using to load the rows into the table,

if you are using BCP, then please mention batch size to a appropriate value, as 6 million records going into one single transaction will create a very big transaction, instead of that you can create a batch size of 50,000 and if any batch fails then truncate the whole table before loading again.

For reading more about batch size, please go to through page (the -b option) http://msdn.microsoft.com/en-us/library/ms162802.aspx

Upvotes: -1

pmbAustin
pmbAustin

Reputation: 3980

Make sure you update your statistics on all your indexes... and it probably wouldn't hurt to shrink your log file. There are articles on how to do both all over the internet. But that's my best guess at what's going on.

Upvotes: -1

Related Questions