asked
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 )
We finally got it to work using SSIS and executing it in batches of 10000 records.
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.
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.
11 People are following this question.
