SQL 插入一行还是多行数据? [英] SQL Insert one row or multiple rows data?
问题描述
我正在开发一个控制台应用程序，将数据插入到 MS SQL Server 2005 数据库中.我有一个要插入的对象列表.这里我以 Employee 类为例:
I am working on a console application to insert data to a MS SQL Server 2005 database. I have a list of objects to be inserted. Here I use Employee class as example:
List<Employee> employees;
我能做的是像这样在某个时间插入一个对象:
What I can do is to insert one object at time like this:
foreach (Employee item in employees)
{
string sql = @"INSERT INTO Mytable (id, name, salary)
values ('@id', '@name', '@salary')";
// replace @par with values
cmd.CommandText = sql; // cmd is IDbCommand
cmd.ExecuteNonQuery();
}
或者我可以像这样构建一个阻止插入查询:
Or I can build a balk insert query like this:
string sql = @"INSERT INTO MyTable (id, name, salary) ";
int count = employees.Count;
int index = 0;
foreach (Employee item in employees)
{
sql = sql + string.format(
"SELECT {0}, '{1}', {2} ",
item.ID, item.Name, item.Salary);
if ( index != (count-1) )
sql = sql + " UNION ALL ";
index++
}
cmd.CommandType = sql;
cmd.ExecuteNonQuery();
我猜后一种情况将一次插入数据行.但是，如果我有几ks的数据，SQL查询字符串有限制吗?
I guess the later case is going to insert rows of data at once. However, if I have several ks of data, is there any limit for SQL query string?
就性能而言，我不确定一次插入多行是否比一次插入一行数据更好?
I am not sure if one insert with multiple rows is better than one insert with one row of data, in terms of performance?
有什么更好的建议吗?
推荐答案
实际上，按照您编写的方式，您的第一个选项会更快.
Actually, the way you have it written, your first option will be faster.
你的第二个例子有问题.您正在执行 sql = + sql + 等.这将导致为循环的每次迭代创建一个新的字符串对象.(查看 StringBuilder 类).从技术上讲，您也将在第一个实例中创建一个新的字符串对象，但不同之处在于它不必复制前一个字符串选项中的所有信息.
Your second example has a problem in it. You are doing sql = + sql + etc. This is going to cause a new string object to be created for each iteration of the loop. (Check out the StringBuilder class). Technically, you are going to be creating a new string object in the first instance too, but the difference is that it doesn't have to copy all the information from the previous string option over.
按照您的设置方式，当您最终发送大量查询时，SQL Server 将不得不潜在地评估它，这肯定需要一些时间来弄清楚它应该做什么.我应该说，这取决于您需要执行的插入次数.如果 n 很小，你可能会没事，但随着它的增长，你的问题只会变得更糟.
The way you have it set up, SQL Server is going to have to potentially evaluate a massive query when you finally send it which is definitely going to take some time to figure out what it is supposed to do. I should state, this is dependent on how large the number of inserts you need to do. If n is small, you are probably going to be ok, but as it grows your problem will only get worse.
由于 SQL Server 处理批处理事务的方式，批量插入比单个插入更快.如果您打算从 C# 插入数据，您应该采用第一种方法并将每 500 次插入到事务中并提交，然后执行下 500 次，依此类推.这也有一个好处，如果批处理失败，您可以捕获这些并找出问题所在，然后重新插入那些.还有其他方法可以做到这一点，但这肯定会比提供的两个示例有所改进.
Bulk inserts are faster than individual ones due to how SQL server handles batch transactions. If you are going to insert data from C# you should take the first approach and wrap say every 500 inserts into a transaction and commit it, then do the next 500 and so on. This also has the advantage that if a batch fails, you can trap those and figure out what went wrong and re-insert just those. There are other ways to do it, but that would definately be an improvement over the two examples provided.
var iCounter = 0;
foreach (Employee item in employees)
{
if (iCounter == 0)
{
cmd.BeginTransaction;
}
string sql = @"INSERT INTO Mytable (id, name, salary)
values ('@id', '@name', '@salary')";
// replace @par with values
cmd.CommandText = sql; // cmd is IDbCommand
cmd.ExecuteNonQuery();
iCounter ++;
if(iCounter >= 500)
{
cmd.CommitTransaction;
iCounter = 0;
}
}
if(iCounter > 0)
cmd.CommitTransaction;
这篇关于SQL 插入一行还是多行数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
