Upload to database more quickly

Question

I have some code that at the end of the program's life, uploads the entire contents of 6 different lists into a database. The problem is, they're parallel lists with about 14,000 items in each, and I have to run an Insert query for each of their separate item(s). This takes a long time, is there a faster way to do this? Here's a sample of the relevant code:

    public void uploadContent()
    {
        var cs = Properties.Settings.Default.Database;
        SqlConnection dataConnection = new SqlConnection(cs);
        dataConnection.Open();

        for (int i = 0; i < urlList.Count; i++)
        {
            SqlCommand dataCommand = new SqlCommand(Properties.Settings.Default.CommandString, dataConnection);
            try
            {
                dataCommand.Parameters.AddWithValue("@user", userList[i]);
                dataCommand.Parameters.AddWithValue("@computer", computerList[i]);
                dataCommand.Parameters.AddWithValue("@date", timestampList[i]);
                dataCommand.Parameters.AddWithValue("@itemName", domainList[i]);
                dataCommand.Parameters.AddWithValue("@itemDetails", urlList[i]);
                dataCommand.Parameters.AddWithValue("@timesUsed", hitsList[i]);

                dataCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                using (StreamWriter sw = File.AppendText("errorLog.log"))
                {
                    sw.WriteLine(e);
                }
            }

        }
        dataConnection.Close();
    }

Here is the command string the code is pulling from the config file:

CommandString:

INSERT dbo.InternetUsage VALUES (@user, @computer, @date, @itemName, @itemDetails, @timesUsed)

Answer 1

1. Make Command and parameter creation outside of for (int i = 0; i < urlList.Count; i++)
2. Also Create insert within a transaction
3. If it possible create a Stored Procedure and pass parameters as DataTable.

Answer 2

As mentioned in @alerya's answer, doing the following will help (added explanation here)

1) Make Command and parameter creation outside of for loop Since the same command is being used each time, it doesn't make sense to re-create the command each time. In addition to creating a new object (which takes time), the command must also be verified each time it is created for several things (table exists, etc). This introduces a lot of overhead.

2) Put the inserts within a transaction Putting all of the inserts within a transaction will speed things up because, by default, a command that is not within a transaction will be considered its own transaction. Therefore, every time you insert something, the database server must then verify that what it just inserted is actually saved (usually on a harddisk, which is limited by the speed of the disk). When multiple INSERTs are within one transactions, however, the check only needs to be performed once.

The downside to this approach, based on the code you've already shown, is that one bad INSERT will spoil the bunch. Whether or not this is acceptable depends on your specific requirements.

Aside Another thing you really should be doing (though this won't speed things up in the short term) is properly using the IDisposable interface. This means either calling .Dispose() on all IDisposable objects (SqlConnection, SqlCommand), or, ideally, wrapping them in using() blocks:

using( SqlConnection dataConnection = new SqlConnection(cs) 
{
    //Code goes here
}

This will prevent memory leaks from these spots, which will become a problem quickly if your loops get too large.

Answer 3

Sending INSERT commands one by one to a database will really make the whole process slow, because of the round trips to the database server. If you're worried about performance, you should consider using a bulk insert strategy. You could:

1. Generate a flat file with all your information, in the format that BULK INSERT understands.
2. Use the BULK INSERT command to import that file to your database (http://msdn.microsoft.com/en-us/library/ms188365(v=sql.90).aspx).

Ps. I guess when you say SQL you're using MS SQL Server.

Answer 4

Why don't you run your uploadContent() method from a separate thread.
This way you don't need to worry about how much time the query takes to execute.