C# SQL 插入命令

发布于 2024-12-17 10:42:18 字数 529 浏览 0 评论 0原文

谁能告诉我以下两种插入记录的方法可以创造更好的性能?

情况1

SqlCommand cmd = new SqlCommand();

for (int i = 0; i < 10000; i++)
{
  cmd = new SqlCommand("insert into test(id, name) value('" + i + "', '" + i + "')");
  cmd.ExecuteNonQuery();
}

情况2

string sql = null;

for (int i = 0; i < 10000; i++)
{
  sql += "insert into test(id, name) value('" + i + "', '" + i + "')";
}

SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();

Can anyone tell me the following 2 ways of inserting record creates better performance?

Case 1

SqlCommand cmd = new SqlCommand();

for (int i = 0; i < 10000; i++)
{
  cmd = new SqlCommand("insert into test(id, name) value('" + i + "', '" + i + "')");
  cmd.ExecuteNonQuery();
}

Case 2

string sql = null;

for (int i = 0; i < 10000; i++)
{
  sql += "insert into test(id, name) value('" + i + "', '" + i + "')";
}

SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

[浮城] 2024-12-24 10:42:18

首先:停止将您的 SQL 代码连接在一起!这是对世界各地的黑客的邀请,通过 SQL 注入攻击您!请改用参数化查询

我会使用这个解决方案:使用参数化查询创建一个单个SqlCommand,并执行它:

string stmt = "INSERT INTO dbo.Test(id, name) VALUES(@ID, @Name)";

SqlCommand cmd = new SqlCommand(smt, _connection);
cmd.Parameters.Add("@ID", SqlDbType.Int);
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100);

for (int i = 0; i < 10000; i++)
{
    cmd.Parameters["@ID"].Value = i;
    cmd.Parameters["@Name"].Value = i.ToString();

    cmd.ExecuteNonQuery();
}

或使用SqlBulkCopy,特别是如果您要插入甚至超过 10'000 行。

First of all: STOP concatenating together your SQL code!! This is an invitation to hackers everywhere to attack you with SQL injection! Use parametrized queries instead!

I would use this solution: create a single SqlCommand with a parametrized query, and execute that:

string stmt = "INSERT INTO dbo.Test(id, name) VALUES(@ID, @Name)";

SqlCommand cmd = new SqlCommand(smt, _connection);
cmd.Parameters.Add("@ID", SqlDbType.Int);
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100);

for (int i = 0; i < 10000; i++)
{
    cmd.Parameters["@ID"].Value = i;
    cmd.Parameters["@Name"].Value = i.ToString();

    cmd.ExecuteNonQuery();
}

or use SqlBulkCopy, especially if you're inserting even more than 10'000 rows.

请恋爱 2024-12-24 10:42:18

第二种方法看起来比第一种方法更快,因为您立即发送 INSERT 命令。第一个中,每个 ExecuteNonQuery 都会到 SQL Server 进行一次往返。

但您应该尝试批量插入命令:BULK INSERT (Transact-SQL),我想您会获得比您提供的任何一个选项更好的性能。

[]的

The second approach looks faster than #1 because you send the INSERT commands at once. In the first there's a round trip to the SQL server for each ExecuteNonQuery.

But you should try the bulk insert command: BULK INSERT (Transact-SQL), I guess you'll get a better performance than any one of the options you provided.

[]'s

我一向站在原地 2024-12-24 10:42:18

应该指出的是,按原样,这两种情况都行不通。

情况 #1 需要指定连接。

情况 #2 要求您以分号结束语句,以便运行多个命令,如下所示:

string sql = null;

for (int i = 0; i < 10000; i++)
{
  sql += "insert into test(id, name) value('" + i + "', '" + i + "');";
}

SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();

最终,最好的方法是您自己在数千行上进行测试。我的猜测是案例 #2 的性能会更好,因为它不仅需要设置单个 SqlCommand 对象,而且只访问数据库一次时间。

It should be noted exactly that as-is, neither case will work.

Case #1 requires a connection to be specified.

Case #2 requires you to end your statements with a semi-colon in order to run multiple commands, like so:

string sql = null;

for (int i = 0; i < 10000; i++)
{
  sql += "insert into test(id, name) value('" + i + "', '" + i + "');";
}

SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();

Ultimately the best way would be for you to just test it yourself on several thousand rows. My guess would be that the Case #2 would be better for performance because not only would it require setting up only a single SqlCommand object, but it only hits the database a single time.

∝单色的世界 2024-12-24 10:42:18

我认为第二个不会起作用。

然而,SQL Server 2008 中有一种语法 在单个 INSERT 语句中插入多行 我认为这会比您提出的两个选项更快:

INSERT INTO test (id, name)
VALUES
('1', 'foo'),
('2', 'bar'),
('3', 'baz')
 -- etc...

但是如果如果您确实想要高性能,请考虑使用 SqlBulkCopy 类。

I don't think the second one will work.

There is however a syntax in SQL Server 2008 for inserting multiple rows in a single INSERT statement and I think that will be faster than both the options you proposed:

INSERT INTO test (id, name)
VALUES
('1', 'foo'),
('2', 'bar'),
('3', 'baz')
 -- etc...

However if you really want high performance, consider using the SqlBulkCopy class.

怼怹恏 2024-12-24 10:42:18

第二个可能更快,因为您最终只有一次往返。两者都同样糟糕,因为您不使用参数化查询

The second one is probably faster, because you end up with a single roundtrip. Both are equally awful, because you do not use parameterized queries.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文