Via a web service, remote computers will be sending a set of rows to insert into our central sql server.
通过web服务，远程计算机将发送一组行以插入到我们的*sql服务器。
What is the best way (performance wise) to insert these rows? There could be anywhere from 50-500 rows to insert each time.
插入这些行的最佳方式(性能方面的)是什么?每次可以插入50-500行。
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.
我知道我可以批量插入数据或将数据格式化为XML，这样插入数据，但我以前从未在企业环境中这样做过。
Updates using wcf web services (or maybe wse not sure yet) and SQL Server 2008 standard.
使用wcf web服务(或者wse还不确定)和SQL Server 2008标准进行更新。
5 个解决方案
#1
6
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 :-)
除非你在一台10年前的电脑上运行，否则50-500行并不多;您可以直接发送SQL语句并将它们直接传输到数据库中，从而获得良好的性能。假设你信任向你发送数据的服务，当然:-)
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.
如果性能真的是一个问题，发送一个bcp文件绝对是在数据库中阻塞数据的最快方法。听起来你的问题是你已经知道怎么做了。
#2
5
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.
仅仅50-500条记录并不构成“大量插入”。大容量插入机制是为大量的数据导入而设计的，这些数据将被立即跟踪并进行备份。
In web service I would simply pass the XML into SQL server. The specifics would be version dependent.
在web服务中，我只需将XML传递到SQL服务器。具体情况将取决于版本。
#3
3
What kind of web service is this?
这是什么类型的web服务?
If it's .Net, usually the best way is to load the input into a DataTable, then shoot it up to the SQL server using the SqlBulkCopy class
如果是。net，通常最好的方法是将输入加载到一个DataTable中，然后使用SqlBulkCopy类将其发送到SQL服务器
#4
3
50-500 rows shouldn't be a problem! There is no need to do performance tuning! Do normal (prepared) SQL Statements in your application.
50-500行应该不是问题!不需要进行性能调优!在应用程序中做正常(准备)的SQL语句。
Don't kill it with complexity and overengineering.
不要用复杂性和过度设计来扼杀它。
When you should insert more than 250.000 rows, you should think about scaling!
当您应该插入超过250.000行时，您应该考虑扩展!
Don't turn of the constraints, you might kill the DB.
不要改变约束条件，你可能会杀死DB。
#5
2
To echo all other answers, 500 rows is no issue for SQL server. If you do need to insert a large number of records, the fastest way is with a built-in stored proc called BulkInsert,
为了响应所有其他答案，对于SQL server来说，500行不是问题。如果您确实需要插入大量记录，最快的方法是使用内置的存储proc BulkInsert，
BulkInsert
which (I Believe) is an entry point to a SQL Server utility designed specifically for doing this called bcp.exe
哪个(我相信)是SQL Server实用工具的入口点，专门为实现这一点而设计，称为bcp.exe
bcp
#1
6
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 :-)
除非你在一台10年前的电脑上运行，否则50-500行并不多;您可以直接发送SQL语句并将它们直接传输到数据库中，从而获得良好的性能。假设你信任向你发送数据的服务，当然:-)
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.
如果性能真的是一个问题，发送一个bcp文件绝对是在数据库中阻塞数据的最快方法。听起来你的问题是你已经知道怎么做了。
#2
5
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.
仅仅50-500条记录并不构成“大量插入”。大容量插入机制是为大量的数据导入而设计的，这些数据将被立即跟踪并进行备份。
In web service I would simply pass the XML into SQL server. The specifics would be version dependent.
在web服务中，我只需将XML传递到SQL服务器。具体情况将取决于版本。
#3
3
What kind of web service is this?
这是什么类型的web服务?
If it's .Net, usually the best way is to load the input into a DataTable, then shoot it up to the SQL server using the SqlBulkCopy class
如果是。net，通常最好的方法是将输入加载到一个DataTable中，然后使用SqlBulkCopy类将其发送到SQL服务器
#4
3
50-500 rows shouldn't be a problem! There is no need to do performance tuning! Do normal (prepared) SQL Statements in your application.
50-500行应该不是问题!不需要进行性能调优!在应用程序中做正常(准备)的SQL语句。
Don't kill it with complexity and overengineering.
不要用复杂性和过度设计来扼杀它。
When you should insert more than 250.000 rows, you should think about scaling!
当您应该插入超过250.000行时，您应该考虑扩展!
Don't turn of the constraints, you might kill the DB.
不要改变约束条件，你可能会杀死DB。
#5
2
To echo all other answers, 500 rows is no issue for SQL server. If you do need to insert a large number of records, the fastest way is with a built-in stored proc called BulkInsert,
为了响应所有其他答案，对于SQL server来说，500行不是问题。如果您确实需要插入大量记录，最快的方法是使用内置的存储proc BulkInsert，
BulkInsert
which (I Believe) is an entry point to a SQL Server utility designed specifically for doing this called bcp.exe
哪个(我相信)是SQL Server实用工具的入口点，专门为实现这一点而设计，称为bcp.exe
bcp