0

I have a large asp.net mvc application that runs on a database that is rapidly growing in size. When the database is empty, everything works quickly, but one of my tables now has 350K records in it and an insert is now taking 15s. Here is a snippet:

            foreach (var packageSheet in packageSheets)
            {
                // Create OrderSheets
                var orderSheet = new OrderSheet { Sheet = packageSheet.Sheet };

                // Add Default Options
                orderSheet.AddDefaultOptions();

                orderSheet.OrderPrints.Add(
                    new OrderPrint
                    {
                        OrderPose = CurrentOrderSubject.OrderPoses.Where(op => op.Id == orderPoseId).Single(),
                        PrintId = packageSheet.Sheet.Prints.First().Id
                    });

                // Create OrderPackageSheets and add it to the order package held in the session
                var orderPackageSheet =
                    new OrderPackageSheet
                    {
                        OrderSheet = orderSheet,
                        PackageSheet = packageSheet
                    };

                _orderPackageRepository.SaveChanges();
                ...
            }

When I SaveChanges at this point it takes 15s the on the first loop; each iteration after is fast. I have indexed the tables in question so I believe the database is tuned properly. It's the OrderPackageSheets table that contains 350K rows.

Can anyone tell me how I can optimize this to get rid of the delay?

Thank you!

Michael Klassen
  • 70
  • 1
  • 6
  • 1
    You need to figure out what is slow. I suggest profiling the database to see if your inserts are slow or if it's your code. – DavidG Sep 29 '14 at 12:45
  • I would initially be looking at SQL Profiler (or equivalent) to establish whether the time you are seeing is due to the database itself or not. – dougajmcdonald Sep 29 '14 at 12:46
  • 1
    15s ist a lot af time. Do you have any triggers with some complicated logic on the target tables? – Stefan Michev Sep 29 '14 at 12:46
  • Make sure you have your indexes setup correctly. – mxmissile Sep 29 '14 at 13:36
  • I would say someone needs to learn what an index is. We likely talk of foreign key relationships without the corresponding index resulting in table scans to validate the data integrity. Which is a serious beginner mistake. Check http://use-the-index-luke.com/ – TomTom Sep 29 '14 at 13:45
  • Have you tried using `SaveChangesAsync()`? – Andres Ramos Sep 29 '14 at 23:35

2 Answers2

-1

EF can be slow if you are inserting a lot of rows at same time.

context.Configuration.AutoDetectChangesEnabled = false; wont do too much for you if this is really web app

You need to share your table definition and for instance you can use Simple recovery model which will improve insert performances.

Or, as mentioned, if you need to insert a lot of rows use bulk inserts

Goran
  • 83
  • 1
  • 2
  • 10
  • -1. Hardly - he talks about the db slow. You surely do not assume he loads the whole table for every item he inserts. – TomTom Sep 29 '14 at 13:46
-2

If the number of records is too high ,You can use stored procedure instead of EF. If you need to use EF itself ,Disable auto updating of the context using

context.Configuration.AutoDetectChangesEnabled = false;

and save the context after the loop

Check these links

Efficient way to do bulk insert/update with Entity Framework

http://weblog.west-wind.com/posts/2013/Dec/22/Entity-Framework-and-slow-bulk-INSERTs

Community
  • 1
  • 1
Prasanth V J
  • 1,126
  • 14
  • 32
  • Man, it is 2014. SP ordynamic SQL makes no difference for a simple SQL statment since - SQL Server 7 (!). I suggest you learn the basics of why SPs are good and when before giving ridiculous advice. I do data manipulation with EF on tables with billions of rows (seriously) without any problem because it does not depend on the number of records in the table. – TomTom Sep 29 '14 at 13:47