optimize insert query on sql server 2008

Question

I recently built an app to test the speed difference between writing 100000 lines to a text file and 100000 inserts into a database

Here is the heart of the code

    private void RunTestBtn_Click(object sender, RoutedEventArgs e)
    {
        Stopwatch FFtimer = new Stopwatch();
        FFtimer.Start();
        RunFFTest();
        FFtimer.Stop();
        TimeSpan FFts = FFtimer.Elapsed;
        string FFelapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
        FFts.Hours, FFts.Minutes, FFts.Seconds,
        FFts.Milliseconds / 10);
        FFLabel.Content = "Flat File: " + FFelapsedTime;

        Stopwatch Datatimer = new Stopwatch();
        Datatimer.Start();
        DataFFTest();
        Datatimer.Stop();
        TimeSpan Datats = Datatimer.Elapsed;
        string DataelapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
        Datats.Hours, Datats.Minutes, Datats.Seconds,
        Datats.Milliseconds / 10);
        DBLabel.Content = "Database: " + DataelapsedTime;

    }
    void RunFFTest()
    {
        using(StreamWriter writer = new StreamWriter(@"F:\test\FFtest.txt"))
        {
            for(int i = 0; i< 100000; i++)
            {
            writer.WriteLine("test");
            }

        }
    }

    void DataFFTest()
    {
        using (SqlConnection conn = new SqlConnection("Data Source=EMMY;Initial Catalog=MyDB;User Id=SqlServiceUser;Password=MyPassword;"))
        {
            conn.Open();
            for (int i = 0; i < 100000; i++)
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "INSERT INTO TestTable VALUES ('Test')";
                    cmd.ExecuteNonQuery();
                }
            }
        }

    }

The end result is that the Flat file writing took 1 millisecond and the sql inserts took nine minutes and forty one seconds. I know the database will take longer, but is there any way to speed this up?

score 2 · Accepted Answer · answered Nov 19 '12 at 15:54

2

Possible options ...

Use transactions (do multiple inserts per transaction)
Use SqlBulkCopy API

answered Nov 19 '12 at 15:54

cbranch

4,709
2
27
25

So in the future we are planning to do inserts on different things, so I just want a way to speed up inserts or the database in general (if thats possible) – Badmiral Nov 19 '12 at 16:03
If by "different things", you mean multiple tables, the same advice still applies. A single transaction may include DML statements for multiple tables. In the case of `SqlBulkCopy`, you'd have to execute a separate statement per table. So, for example, if you have 5 tables, you would execute 5 SqlBulkCopy operations. In both cases, the underlying idea is to insert in batches so that you minimize the overhead of the locking and the transaction log. – cbranch Nov 19 '12 at 16:24
Oops sorry, should be more clear. I meant to say it would not be inserting "test" over and over, it would be inserting a variety of strings and integers, which seems like it would not work for the sqlbulkcopy – Badmiral Nov 19 '12 at 16:31
1

You can still use `SqlBulkCopy`. The easiest way is probably to create a `DataTable` containing the rows you want to insert, then call `SqlBulkCopy` with the `DataTable` as the source. Here's an example: http://myadventuresincoding.wordpress.com/2012/05/27/c-bulk-copying-data-into-ms-sql-server-with-datatables/ – cbranch Nov 19 '12 at 16:37

score 0 · Answer 2 · edited Nov 19 '12 at 16:08

0

Yes, do it as one single insert statement, rather than many.

INSERT INTO TestTable VALUES ('Test'), ('Test'), ('Test') ....";

edited Nov 19 '12 at 16:08

marc_s

732,580
175
1,330
1,459

answered Nov 19 '12 at 15:54

Justin Harvey

14,446
2
27
30

There is a maximum of 1,000 rows that can be inserted in this way and [it might actually be slower.](http://stackoverflow.com/q/8635818/73226) – Martin Smith Nov 19 '12 at 15:56
That article takes no account of CreateCommand or ExecuteNonQuery as is being done within the loop here. I think it would be worth a try as it would not take at all long to test under the conditions in question. – Justin Harvey Nov 19 '12 at 16:09
The OP there says " I've got similar results running it from C# code using SqlClient, which is even more suprising considering all the DAL layers roundtrips" for short strings like `test` with lots of duplicates things may be better though! – Martin Smith Nov 19 '12 at 16:10

optimize insert query on sql server 2008

2 Answers2