Menu
Via a web service, remote computers will be sending a set of rows to insert into our central sql server.
What is the best way (performance wise) to insert these rows? There could be anywhere from 50-500 rows to insert each time.
I know I can do a bulk insert or format the data as XML that insert it that way, but I've never done this in an enterprise setting before.
Updates using wcf web services (or maybe wse not sure yet) and SQL Server 2008 standard.
685
Below are some good ways to improve BULK INSERT operations : Using TABLOCK as query hint. Dropping Indexes during Bulk Load operation and then once it is completed then recreating them. Changing the Recovery model of database to be BULK_LOGGED during the load operation.
The 1000 limit only applies when you are passing in the rows using a values statement - if you were inserting based on a select from a table then there is no limit. The row constructor, using VALUES, has a limit of up to 1000 rows. You can split the insert in two chuncks, or you can use SELECT ... UNION ALL instead.
It takes about 3 mins to insert 1 million rows if I run it in the SQL server and take about 10 mins if I use C# program to connect from my desktop. The tableA has a clustered index with 2 columns. My target is to make the insert as fast as possible (My idea target is within 1 min).
Unless you're running on a 10 year-old computer, 50-500 rows isn't very many; you could literally send over SQL statements and pipe them directly into the database and get great performance. Assuming you trust the services sending you data, of course :-)
If performance really is an issue sending over a bcp file is absolutely the fastest way to jam data in the database. It sounds from your question that you already know how to do this.
174
A mere 50-500 records does not constitute a "bulk insert". The bulk insert mechanism is designed for really massive import of data which is to be immediately followed up with a back up.
In web service I would simply pass the XML into SQL server. The specifics would be version dependent.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
