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();
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.
AddWithValue
Add() even if VS gives me warning.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
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.
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.
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.
SqlCommandinstances and executing them one by one is just as fast as creating a single instance and executing it just once???