many individual inserts slow - need suggestions
-
I have a table that contains about 20,000 rows. Periodically, an application wants to do about 2,500 inserts, one row at a time, using individual sql calls, into this table. I'm looking for suggestions to speed this process up. One idea that I'd like input on: remove the clustered index, do all of the inserts, then recreate the clustered index. Would this likely give me any faster performance? Is this likely to cause me other problems. It is normally run off peak hours, so there should be few others accessing the db at this time. I also considered a bulk insert from an array, but it seems like the application would need to have sysadmin privileges, which it does not. Is this correct? Does anyone have any other suggestions?
Thank you.
-
- Create a temporary table with the same columns as the target table, but with no indexes (to speed inserts)
- Do the one-by-one inserts into the temporary table
- When those are done, do a single insert to copy the contents of the temporary table into the target table (INSERT INTO TTarget SELECT Col1, Col2, ..., Coln FROM TTarget_temp)
This assumes that the individual inserts don't depend on the target table being always up to date, of course.
-
Thank you for the suggestion. I was wondering if it bought me anything in performance to do it this way over temporarily dropping the clustered index? Also, how does this index work during the insert ... from statement you suggested? (Does it need to move the data as every row is inserted, or does it re-do the index once after all of the inserts are complete?)
-
I had faced earlier a similar situation and asked the Programmer to build a XML doc and pass it to a SP. In the sp using something like this
EXEC
sp_xml_preparedocument @OpenXMLID OUT, @OffsetXML
INSERT MyTable(Col1)
SELECT Col1 FROM
OPENXML(@OpenXMLID, '/Root/Element', 2)
...
@OffsetXML is the input text parameter having the xml data.
This inserts all the data at one go and believe me the fastest I have seen
bm21 -
Hey Jdalonzo
I work with tables that typicaly get Millions of rows inserted into them at a time. BOL and I suggest dropping and recreating the clustered index if:
As a general guide, the following table shows suggested figures for the amount of data to be added to a table for various types of indexes. If you exceed these percentages, you may find it faster to drop and re-create the indexes.
Indexes Amount of data added Clustered index only 30% Clustered and one nonclustered index 25% Clustered and two nonclustered indexes 25% Single nonclustered index only 100% Two nonclustered indexes 60% (SQL BOL: Optimizing Bulk Copy Performance)
Also, when you have a large number of inserts to do, particularly if they are coming from a flat file (csv / txt / tab delimted, etc) use BCP, or even better the BULK INSERT command. SQL Server will suck all the data in in one go, without all the mess of mulitple insert statements. It's faster than multi-inserts, and uses less transaction log.
Julian Kuiters
juliankuiters.id.au -
Are they individual "insert into" commands? If so, consider using a stored procedure instead. This saves on compilation time for each insert. We gained considerable performance improvements inserting 20 million rows every month using stored procedures. We don't drop the clustered index.
Do you have any other indexes on the table? If you do, dropping the clustered index will cause the nonclustered indexes to be rebuilt. Its always better to drop non-clustered first, then the clustered index and then rebuild clustered then non-clustered.
-
Thank you for all of your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply