0

i have created a simple database (just one table) with EF Code First approach. I am hosting the database on Azure. Now i want to insert some data from my local machine but the connection (via internet) is very slow. Even when i use normal SqlCommands without EF to insert data the performance is still slow but about 10 times faster than EF.

Here is my Insert Code:

            using (InfoContext ctx = new InfoContext(dbs))
            {
                ctx.Database.ExecuteSqlCommand("DELETE FROM Entries");
                ctx.dbsEvents.AddRange(list);
                ctx.SaveChanges();
            }

From my point of view the code as simple as it is seems right, so the problem must be the azure db connection. I am using the Basic Tier with 5 DTU's which should be enough i guess. Any ideas what i can do to increase azure db connection speed or whats another, faster way to upload data to azure db?

David Makogon
  • 69,407
  • 21
  • 141
  • 189
  • The Basic Tier may have limitations causing the slowness you're observing. Not ideal, but you can consider scaling up your database to a larger SKU and scaling back down after your upload is complete. – andrea-lam-MSFT May 11 '16 at 21:11

1 Answers1

0

My guess would be that you are far away from the Azure Database Server, and when you add a list of objects in EF, it will send a separate

insert .....
select @@identity // @@rowsaffected

So that it makes sure that it got inserted successfully. It therefore means it waits for a reply for each insert, before sending the next insert statement off.

If you don't need the Identity value

You could try SqlBulkCopy (BCP) into the database, although I am not 100% certain you can into Azure, I would like to think you can.

SqlBulkCopy - MSDN

There is also a Batch Insert helper, but unrelated to EF, will try to

If you do need Identity Values

have a look Fastest Way of Inserting in Entity Framework

Community
  • 1
  • 1
Michal Ciechan
  • 13,492
  • 11
  • 76
  • 118
  • i was hoping there is a simple way to speed up azure sql connection, anyway the sqlcommands are also too slow, i need to find a way to upload data very fast. –  May 11 '16 at 07:13
  • any ideas how i can upload data fast to azure SQL? –  May 11 '16 at 10:37