locked
Entityframework with Azure SQL Database VERY SLOW RRS feed

  • Question

  • Hi,

    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 (geographical distance between me and the datacenter). 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 from my local c# program.

    KR Manuel

    Wednesday, May 11, 2016 8:33 AM

All replies

  • Hi blenet, 

    Few questions 

    • Why are you deleting entities in your insert code? 
    • How large is the table that you are inserting into?
    • Can you check to see how many DTUs are you using? 

    -Casey

    Wednesday, May 11, 2016 4:44 PM
  • Good day blenet,

    This short code includes more issues, than the number of lines. I will try to go over some points, which might help you understand and potentially can help you to improve your application performance.

    >> Please clarify if this what you want to do.

    As much as I can see from your code you first delete all the existing rows using:
    ctx.Database.ExecuteSqlCommand("DELETE FROM Entries");
    And then you insert the new list of data using:
    ctx.dbsEvents.AddRange(list);

    >> SQL Server Query performance: Using the query "DELETE FROM Entries" instead of "TRUNCATE TABLE Entries"

    * There are some restrictions and limitation using truncate but I assume that these are not relevant for you.

    I see that you are using DELETE without any filter, which mean that you delete all your data (unless this is SQL 2016 row level security which filter the data, or a view that filter the data, or something like this which is not a simple direct table delete, for example).

    TRUNCATE TABLE statement deallocating the data pages (data is stored in units named "page") used by the table. The only record of the truncation in the transaction logs is the page deallocation. DELETE TABLE statements delete rows one at a time, logging each row in the database transaction log, as well as maintaining log sequence number (LSN) information. Therefore using DELETE cost resources - time, locks, disc space...

    * TRUNCATE resets the IDENTITY back to the original SEED, but you can change this number with a simple statement if needed.

    >> Using the method DbSet.AddRange AutoDetectChangesEnabled is set to true by default, which mean that DetectChanges will be called once before adding any entities. Usually this is advantage sine it will not be called again, but in your case you clean all the data before, and there is no reason for this.

    >> Moving back and foreword from using "pure query" to/from using "EF queries", instead using built in options if you choose to use EF.

    * To clarify, personally i would not use EF or any other ORM probably. I would prefer to use pure queries for most cases.

    I cannot understand why you use AddRange method but you cannot use the equivalent method RemoveRange in order to delete rows instead of using a pure query with the ExecuteSqlCommand method. Both method works in EF6. In fact you should have think about using the Entity Framework Extended Library, which bring you much better option with the Delete() method, with simple code like:
    context.Users.Delete(); // Without filter
    context.Users .Where(u => u.FirstName == "firstname") .Delete(); // You can use filter with the where method

    I am not saying that this is the best option, but it is better then your approach of moving back and foreword from using pure query to EF only for the sake of missing method, in my opinion (for better option read point 1 regarding truncate, and last point regarding using ORM in general).

    >> SQL Server Query performance: BULK insert vs simple insert

    * I highly recommend to use google in order to find good explanation regarding the differences of using BULK INSERT vs simple INSERT statement.

    Please check this graph which is taken from the link EntityFramework.BulkInsert method! the time can be 0.01% of the time using AddRange method !

    >> I can add more points :-) but I don't have time... Let's jump to the most importent point

    >> Entity Framework (or any other ORM) does not come to bring you performance but to make the developer life simpler and faster! If you want performance then you should write queries and check SQL Server Execution Plan, and improve these according to your DDL+DML.

    in conclusion

    1. if you need to delete all rows then you should prefer to use TRUNCATE instead of DELETE

    2. Use bulk insert if you need to insert lot of rows

    I hope this was useful :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]




    • Edited by pituachMVP Wednesday, May 11, 2016 8:47 PM
    Wednesday, May 11, 2016 6:55 PM
  • Hi blenet, 

    Few questions 

    • Why are you deleting entities in your insert code? 
    • How large is the table that you are inserting into?
    • Can you check to see how many DTUs are you using? 

    -Casey

    >> Why are you deleting entities in your insert code? 

    He is cleaning the entire old data BEFORE he insert the data using the method AddRange :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, May 11, 2016 6:57 PM
  • Hi Ronen,

    thanks for your Help.

    First of all i do have more tables than just one. I am not using truncate because my table has foreignkeys and i do not want to remove and add the key afterwards as truncate is not working with foreignkeys.

    The table i am writing and deleting contains data for a website to display. Most of the data change, i possibly could do an update but i am pretty sure its faster to delete all entries and insert them again instead of searching for ids and updating. Anyway the table normally has not more than 40 entries.

    I changed my code to Delete() instead of executenonquery to compare the perfomance. It had no effect on the Performance. Anyway, i would expect Performance issues when changing 1000 or more entries but not 20.

    I would really like to use Entityframework as i have about 20 tables and i really do not want to write 20 INSERT / DELETE / UPDATE Sql Commands.

    The fact is if i use the same Code on my local SqlServer it's fast without any delays. From my Point of view the bottleneck must be the azure Environment which is virtual an shared between many users.

    KR Manuel

    Thursday, May 12, 2016 7:42 AM
  • Hi blenet,

    >> i do not want to remove and add the key

    This is actually something VERY common and very useful approach for lot of cases.  (1) Disable / drop indexes / constraints on target table. (2) indert data using bulk insert, with table lock hint (TABLOCKX). Sometimes we need to insert the data in batches. (3) and in the end Re-enable / re-create indexes / constraints on target table...

    >> Anyway the table normally has not more than 40 entries.

    DO you mean that you have 40 rows in the table?!?
    What is the size of the rows? How much data do you post?

    >> I changed my code to Delete() instead of executenonquery to compare the perfomance.

    This comment was not related to SQL Server performance but with clean coding. Using ORM will never be faster then sending direct query, assuming the query is well written. As a developer (actually as the application architect) you should choose your approach and keep it during the entire project, if you can and if it fit. A person that know 70 different developing languages and 30 technologies. should not write each method in the project using different language and each module using different tech, for example. Choose your approach for each project and keep it to the end :-)

    >> I would really like to use Entityframework

    OK, then this is your project main approach :-)

    >> The fact is if i use the same Code on my local SqlServer it's fast without any delays.

    This is new information, which can help monitor the issue :-)
    But, it is expected to get worse performance using remote server, the more so when you are using external service like Azure which limits the performance by-design, according to your configuration.

    >>(from original post) Even when i use normal SqlCommands without EF to insert data the performance is still slow

    This is probably the limitation of remote server + the Azure limitation as mentioned above.

    >>  but about 10 times faster than EF.

    This is expected as I mentioned :-)
    ORM is is not for performance but for fast codding.

    >> I am using the Basic Tier with 5 DTU's which should be enough i guess.

    Why guess?!? Why not to check :-)
    One of the main (some people will say the only) advantage using the azure, is the ability to change "your server" virtual performance and go back in seconds, while paying according. At this time I would recommend to test it on more powerful configuration server :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, May 13, 2016 9:37 PM