x

Where to look when inserts are slow

First, a little background:

I had an insert script that inserts a few hundred thousand rows into a categories table. It was taking a couple hours, and I stopped it because I thought it was taking too long. This crashed the DB and it went into recovery mode. Our network admin restored from a backup to another DB and I ran the categories script again. It ran fine and only took a few minutes. Now on that same DB that ran the previous script fine, I have another script for inserting a few hundred thousand products. Again, it's running slow. I stopped it at 5 hours but it didn't crash... I read somewhere to check the locks and it had 34,000. I don't really understand what that means though.

So, I'm not a DB admin, and I have no idea why this is happening or what to look for. Can I have some ideas on what to look for to see what this issue is?

HERE IS THE INSERT SCRIPT:

INSERT INTO Products (StoreId, Name, Price, CostOfGoods, MSRP, Weight, Length,      
Width, Height, Sku, ShippableId, 
InventoryModeId, InStock, InStockWarningLevel, Description, CreatedDate,   
 LastModifiedDate, IsFeatured, 
IsProhibited, AllowReviews, AllowBackorder, ExcludeFromFeed, DisablePurchase, 
 MinQuantity, MaxQuantity, VisibilityId, 
IsGiftCertificate, UseVariablePrice, HidePrice, OldItemId)
 SELECT 1 AS StoreId, ItemName, ItemListPrice, ItemCost, 0 AS MSRP, 0 AS Weight, 0 AS   
 Length, 0 AS Width, 0 AS Height, ItemPartNumber, 1 AS ShippableId,
 0 AS InventoryModeId, 0 AS InStock, 0 AS InStockWarningLevel, ItemDescription,   
 GetDate() AS CreatedDate, 
 GetDate() AS LastModifiedDate, 0 AS IsFeatured, 0 AS IsProhibited, 0 AS   
 AllowReviews, 0 AS AllowBackorder, 0 AS ExcludeFromFeed, 0 AS DisablePurchase,
 0 AS MinQuantity, 0 AS MaxQuantity, 0 AS VisibiltyId, 0 AS IsGiftCertificate, 0 AS   
 UseVariablePrice, 0 HidePrice, ItemID
 from Item I where exists
 ( select Part_Number from Parts_Listing PL where   
 I.ItemPartNumber = PL.Part_Number )
more ▼

asked Nov 14 '12 at 03:59 PM in Default

johnstrez77 gravatar image

johnstrez77
210 5 7 7

Can you post the script so that we can see how you are doing the inserts? You might want to consider batching the inserts (say 5000 records at a time) to allow locking/blocking to clear.
Nov 14 '12 at 04:23 PM JohnM
We finally got it to work using SSIS and executing it in batches of 10000 records.
Nov 15 '12 at 02:54 PM johnstrez77
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Lots of things could be impacting this. I will list them out in no particular order.

1) Indexes, when you perform inserts,updates,deletes then the indexes on that table also have to be updated.
2) Locks - if you are inserting records during a heavy user time, you could be causing locking/blocking with your insert as well as being locked/blocked yourself by other users.
3) I/O - depending on the time of day and other processes occuring, you could be competing for I/O from batch processes, high user activity, backups, other systems on shared subsystems, etc
4) Bad code - Depending on how your insert statement is constructed, if you are gathering a result set to insert into another table, are you performing massive table scans? What part of your statement is causing the most I/O or generating waits?
5) What do your performance counters look like for memory, cpu, disk while this is running? How does that compare to other times of the day when you aren't doing the insert?

FYI, killing a job mid way will cause a rollback, if you have a single transaction to insert 500000 rows and at row 300000 you kill it, it will have to rollback the insert of those 300000 records.
more ▼

answered Nov 14 '12 at 04:30 PM

Tim gravatar image

Tim
35.5k 32 40 138

(comments are locked)
10|1200 characters needed characters left
Hi... you can check in the 'Activity Monitor' (For SQL 2008, right click on the instance name in ssms and select the option). Sort the DB name column and check which processes are running on the database that you have executed the insert script on. If there are simultaneous SELECTS or UPDATES going on in the DB, they might be locking out your inserts. Best option to insert BULK data is in Server idle time when there are very few other processes/queries running on the DB.
more ▼

answered Nov 14 '12 at 04:25 PM

DharmanDave gravatar image

DharmanDave
460 7 8 11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x241

asked: Nov 14 '12 at 03:59 PM

Seen: 1081 times

Last Updated: Nov 15 '12 at 02:54 PM