Recreating Table and filling with large pack of data SQL Server

Question

I am building application with online database. The database is not on my computer. It is in myasp.net server.

I've got two questions:

1. This application collects data and after get all, data needs to be sent to this online database. I am open to any solution, including frameworks etc, but I must say that Entity Framework is very slow in my case. My data collection application creating file with inserts values. F.e:

   (4880775 , 18196 , 9 , 1),
   (4880775 , 9026 , 8.49 , 2),
   (4880775 , 4009 , 9.99 , 3),
   

   This file could have (in future) at least 10 mln rows. I have done two tests. One is insert ten times 990 rows using pure SQL query (in VS 2013 right click on database -> new query) and this was something like this:

   declare @i int
   set @i = 0
   while @i < 10
       begin
         set @i += 1
   
       INSERT INTO daneProduktu VALUES 
   
       (4880775 , 18196 , 9 , 1),
       (4880775 , 9026 , 8.49 , 2),
       (4880775 , 4009 , 9.99 , 3),
       ...
       ...
   end
   

And second option was doing the same thing using c# code. I have used Entity Framework

    using (var context = new sklepyEntities())
    {
        context.Database.ExecuteSqlCommand(sb.ToString());
    }

and SqlCommand object

    using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
        {
            cmd.ExecuteNonQuery();
        }

Full code for c# SqlCommand is:

Stopwatch st = new Stopwatch();
st.Start();

for (int i = 0; i < 10; i++)
{
            sb.Clear();
            sb.Append("INSERT INTO daneProduktu VALUES ");
            r = new StreamReader(sciezka);

            while ((line = r.ReadLine()) != null)
            {
                licznik++;
                sb.Append(Environment.NewLine);
                sb.Append(line);
            }

            sb.Length--;
            sb.Append(";");

            using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
            {
                cmd.ExecuteNonQuery();
            }
}

sb.Length--;
sb.Append(";");

using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
{
            cmd.ExecuteNonQuery();
}

st.Stop();

Both are working, but it is so slow..

To compare timings:

• Pure SQL query - ~3s
• C# SQL using SqlCommand - ~13s

There was special prepare file with 990 insert values. I was using the same values in both cases.

Why code using option is so slow? Is there any way to make it faster? OFC using pure inserts is not only option. I can do anything else. I can prepare XML file for this, csv or anything else if this could be faster.

2. Every time, before i do inserts from 1st point, I need to clear table. I was reading about shrinking, that is not good, so I choose to drop and recreate table. After this action there is no less space usage, but when i filling table with inserts, space remains the same. Also I will not need to roll back anything from this table. Is this good way? Or maybe Truncate table will be better?

Answer 1

What I've heard is that sometimes Insert INTO VALUES for many rows is not always the fastest. Have you tried:

INSERT INTO yourTable
SELECT 'Value1' 
UNION ALL 
SELECT 'Value2' 
UNION ALL 
SELECT 'value3 
etc...

Answer 2

I've tried few ways to make this insert, and sqlBulkCopy was fastest. My code is:

using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
                {
                    sqlBulkCopy.BulkCopyTimeout = 600;
                    sqlBulkCopy.DestinationTableName = "daneProduktu";
                    sqlBulkCopy.ColumnMappings.Add("numerProduktu", "numerProduktu");
                    sqlBulkCopy.ColumnMappings.Add("numerSklepu", "numerSklepu");
                    sqlBulkCopy.ColumnMappings.Add("cena", "cena");
                    sqlBulkCopy.ColumnMappings.Add("pozycjaCeneo", "pozycjaCeneo");

                    sqlBulkCopy.WriteToServer(dt);
                }

I'am also using SQL transaction here. Some of you could say "Use BatchSize". I tried, but this option make this insert slower. I must say, that I've got bad upload. I make some time measurments:

BatchSize in Time
0 in 278768ms
500 in 1207129ms
1000 in 817399ms
1500 in 629146ms
2000 in 531632ms
2500 in 480200ms
3000 in 451510ms
3500 in 446899ms
4000 in 407875ms
4500 in 405808ms
5000 in 387078ms
5500 in 360508ms
10000 in 327231ms
20000 in 305282ms
30000 in 305936ms
40000 in 304494ms
50000 in 303541ms
60000 in 303723ms
80000 in 310058ms
100000 in 297835ms

As you can see, 0 batch size is fastest.

To answer my question about INSERT INTO Values():

Probably this all was about sending those inserts via internet connection. In first case I send ONE order, and loop was done on SQL server. In second case, probably I have send 10 orders to server.