SQL Server Throughput

I'm working on a project that involves importing roughly 7 million rows of data from a denormalised MySQL database into a fully-normalised MSSQL database system.

I've written software that transforms the data from the format used in the MySQL DB (imported through large CSV files) and then inserts it into MSSQL, however in my LAN environment against my development server (over gigabit links, with a reasonably specc'd out server) I can never seen to get more than 150 INSERT operations per second.

I'm just doing:

using(SqlConnection con = GetConnection()) {
SqlCommand cmd = con.CreateCommand();
// add parameters to cmd.Parameters here

    foreach(Record r in recordsToInsert) {
        cmd.Parameters["@foo"] = r.Foo;
        cmd.ExecuteNonQuery();
    }
}

So I'm re-using both the connection and command objects, what gives?

I'm going to be using BULK INSERT for the production data, which should hopefully be faster, but does anyone have any suggestions?

If you append more than 1 parameterized statement per command instance you should see better performance than that.  I did that and had over 100,000 records per minute against a dev machine with separate disks for data and log, but just standard IDE HDDs.

2 hours ago,JPeless wrote

If you append more than 1 parameterized statement per command instance you should see better performance than that.  I did that and had over 100,000 records per minute against a dev machine with separate disks for data and log, but just standard IDE HDDs.

So I should do:

StringBuilder cmdText;
foreach(Record r in records) cmdText.Append("INSERT INTO foo ( bar ) ( @bar" + (i++) + ")");
foreach(Record r in records) cmd.Parameters["@bar" + i].Value = r.Bar;

Seems a bit hackish, but I'll give it a go.

23 minutes ago,blowdart wrote

Why don't you just use bulkinsert?

None of the documentation for bulkinsert says what to do in order to escape special characters, such as field terminators. If a field terminator is "," then how do you escape a comma used in a field's value? It doesn't say.

@W3bbo:Now that I looked back at the code we used it just built the insert statements and did some basic sql prepration (like escaping single quotes, etc.) because the data wasn't public so SQL injection wasn't a risk we had to mitigate, so we didn't use parameterized commands, we did just concatenate in a string builder and execute it.  It served our purposes, and for simple data migration, where the inputs are known it may suffice as well.  Other than that, perhaps a DTS/SSIS type package or bulk insert would offer better performance if that is what you are after.

@JPeless: I don't know if SQL Server handles it differently, but parameterized queries are much faster than straight query strings in Oracle because Oracle caches the execution plan of the query and can then just insert thte parameter values. So even with no threat of SQL injection, using parameterized queries is the way to go.

@W3bbo:For what it's worth. Using Sql2005 on my dev machine (so no network involved).

Using your method, but within a transaction, it inserts 15,000 records in 3s.

            var start = DateTime.Now;
            using (SqlConnection con = GetConnection())
            {
                var tran = con.BeginTransaction();
                SqlCommand cmd = con.CreateCommand();
                cmd.Transaction = tran;
                cmd.CommandText = "insert into test (value) values (@value)";
                cmd.Parameters.Add("@value", SqlDbType.Int);

                foreach (int r in recordsToInsert)
                {
                    cmd.Parameters["@value"].Value = r;
                    cmd.ExecuteNonQuery();
                }
                tran.Commit();
            }
            var end = DateTime.Now;
            tbOutput.Text = string.Format("Elapsed time {0}", (end - start));

Using a stored procedure and batching all 15,000 inserts into one command, about 0.4s

            var start = DateTime.Now;
            using (SqlConnection con = GetConnection())
            {
                var tran = con.BeginTransaction();
                SqlCommand cmd = con.CreateCommand();
                cmd.Transaction = tran;
                var sb = new StringBuilder();
                foreach (int r in recordsToInsert)
                {
                    sb.AppendFormat("exec Insert_Test {0};", r);
                }
                cmd.CommandText = sb.ToString();
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            var end = DateTime.Now;
            tbOutput.Text = string.Format("Elapsed time {0}", (end - start));

1 day ago,W3bbo wrote

I'm working on a project that involves importing roughly 7 million rows of data from a denormalised MySQL database into a fully-normalised MSSQL database system.

I've written software that transforms the data from the format used in the MySQL DB (imported through large CSV files) and then inserts it into MSSQL, however in my LAN environment against my development server (over gigabit links, with a reasonably specc'd out server) I can never seen to get more than 150 INSERT operations per second.

I'm just doing:

using(SqlConnection con = GetConnection()) {
SqlCommand cmd = con.CreateCommand();
// add parameters to cmd.Parameters here

    foreach(Record r in recordsToInsert) {
        cmd.Parameters["@foo"] = r.Foo;
        cmd.ExecuteNonQuery();
    }
}

So I'm re-using both the connection and command objects, what gives?

I'm going to be using BULK INSERT for the production data, which should hopefully be faster, but does anyone have any suggestions?

W3bbo:

depending on what the data is like etc.... there are several ways to import data into sql.

one option that may or may not be of use is *IF* you are using server 2008 you can build a text file with an insert command like this:

INSERT INTO [Tablename] ( columns.....)

Values ( list )  ,

(list),

(list),

(list)

that makes a single insert batch to insert many rows at one time.

just another option.

so far as bulk insert just write a simple text file programm to prep the data and then pass it on to the bulk insert tool. also i think there is a bulk insert api for .net as i recall.... have not used it but look for that and see what it does.

see if this helps:

http://blogs.msdn.com/b/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx