April 17, 2003 at 7:45 am
There is a client which needs to insert 100,000 records to a table every minute then what would be the best approach?
Here are Test statistics.
about 80KB data, 5000 rows takes....
ODBC with AUTOCOMMIT OFF = 13263 ms
ODBC with AUTOCOMMIT ON = 20110 ms
ADO with AutoCommit OFF = 16296 ms
ADO with AutoCommit ON = 22016 ms
Query Analyzer with AutoCommit OFF = 1076 ms
Query Analyzer with AutoCommit ON = 6813 ms
I believe I should be able to achieve performance atleast equal to Query analyzer.
Thanks
GOpal
April 17, 2003 at 7:54 am
Have you tried bulk insert or bcp? These are non logged inserts so might be even quicker.
Jeremy
April 17, 2003 at 8:12 am
I agree bulk insert or bcp probably makes sense - if your app works that way. If you're doing 100,000 single record inserts, harder to manage. Might want to consider inserting into a staging table that has no indexes or constraints, then push from there to the main table using a job.
Andy
April 17, 2003 at 8:23 am
It is a kind of data collection application so it doesn't have all 100,000 rows at a time; but Idea is we need fast insert mechanism. So I think bcp may not work for this app. I am thinking about staging table also. Is it a supported feature or just mimic by creating a replica table or original. Here problem is indexes; we have autoIndex on and I am afraid even the staging table will get the indexes.
Please give me little more ideas. I appreciate you guys.
Gopal
April 17, 2003 at 8:40 am
Well, if bcp isn't an option, I agree with Andy's recommendation. If you create a stored proc that performs the staging insert into a copy of the target table, you may see performance at least as good as Query Analyzer.
Now, I'm not clear on what you meant by AutoIndex. Is the PK on the target table an Identity column?
Also, what's the format of the inbound data? Text, SQL Data, Access, Excel, etc...?
SJTerrill
April 17, 2003 at 9:00 am
It is C++/VB application which collects data from various apps and the format of data doesn't make more sense because it is all collected data a row at a time. I tried to put Begin tran and Commit between every 5000 rows and it gave little performance improvement. Next I am going to try staging table also.
Auto Index is a feature of SQL server which crates indexes on the tables by its own optimization mechanism as per usage.
April 17, 2003 at 9:44 am
I tested the Staging table (a copy of the original table schmema with no index, no contraints) doesn't help because perfomance is still same.
Any other ideas please!!
April 17, 2003 at 11:34 am
Cant think of much else. With that many inserts you're going to be creating a lot of pages, doing a lot of logging, so it would help to have the space pre-allocated. If you use a clustered index, you'll have to think carefully about where it goes, could create a bottleneck.
The other way would be to submit multiple batches at once - do parallel inserts.
Andy
April 17, 2003 at 12:18 pm
Any idea or link to the infromation of using ADO for bulk insert/bcp.
I know There is an IRowsetFastLoad interface; but does anybody have sample for using it with VB/ADO.
Thanks for your time.
April 17, 2003 at 1:31 pm
We use XML inserts for this type of mass insert. We format an xml document in chunks of about 1000-5000 records, then call one single XML insert statement. We insert millions of rows every night and it goes pretty quick. You might want to increase your batch size.
Darren
Darren
April 17, 2003 at 1:37 pm
Darren, May you please point me to some link how to use this XML insert mechanism. Or some sample code.
Thanks for your time.
April 17, 2003 at 1:57 pm
CREATE PROCEDURE dbo.spCustomerXMLIM01
@XmlDoc text
AS
DECLARE @docID int
EXEC sp_xml_preparedocument @docID OUTPUT, @XmlDoc
INSERT INTO tCustomer
SELECT * FROM OPENXML(@docID, '/ROOT/spCustomerIS01', 1) WITH tCustomer
EXEC sp_xml_removedocument @docID
==============
The xml document is formatted with the detail records as XML attributes. We format this on the fly so I don't have an example of the XML doc.
Darren
Darren
April 17, 2003 at 2:01 pm
Also, you may find some additional help in BOL under topic "using OPENXML" or "Retrieving and Writing XML Data"
Darren
Darren
April 18, 2003 at 8:10 am
Thanks Darren. I am going to try this stuff.
April 18, 2003 at 9:59 am
You may also want to look at your database settings. For example changing from full logged to bulk logged or simple - this will have reprocutions on your backup, but it will also increase you insert times.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply