Entity Framework 4 : Bad performance with SQL Server 2008

Question

I'am developing a software based on Entity Framework to handle data in a MS SQL Server 2008 database.

[Trouble 1]

I've just tried to insert some small data (about 2 Mb) from my progam to the database : the performance are very bad ! It takes more than 1 minute to insert these datas !

I've try to generate pre-compiled views, I've got the same results :-(

All my code use a business layer (automatically generated from .edmx file with T4 template) to manage data in a service layer. It is very pratical to navigate in the relations of objects.

How can I improve the performance of these inserts with Entity Framework ?

[Trouble 2]

Also, before inserting data in database with SaveChanges() method, I fill my object context with AddObject() method. I add about 100 000 small objects (about 2 Mb) to my object context with AddObject() : it takes a very long time (more than 10 minutes) !

How can I decrease this time ?

UPDATE

My program must save more than 50 Mb in database in less than 2-3 minutes ? Do you think it will be possible with EF ?

Answer 1

You could use the Entity Framework Profiler to check what SQL is being generated. This tool has a 30 day free trial. It also claims that it can do "Analysis and detection of common pitfalls when using Entity Framework".

There is also this article on EF tuning

Edit

Based on your edits EF is not the way to go. You need to do a bulk insert in order to get the data in that fast. Have a look at this link where I helped someone reduce load time from 10 hours to 6 mins.

Answer 2

There are several possibilities here.

1. The database hardware might not be up to the task of handling 100,000 inserts. How many tables are involved? Are there ancillary considerations such as triggers that are firing? Is the database memory constrained?

2. The Web server hardware might not be up to the task of processing that much load. Where is the data originating? How long is it taking to transfer to the web server? How many inserts/sec is the web server actually sending to the database server?

To sum up, you have to profile to figure out exactly where the bottlenecks are. With the information you've given so far it could be anywhere.

You need to run a profiler on the web server, and you need to use SQL Profiler on the database server. Additionally, you should be monitoring both machines CPU, memory, and network usage graphs while loading the data.

Once you have all of that data you should be able to pinpoint where the problem is.

UPDATE
As a side note, EF has to create at least 100,000 objects to hold the data that you are uploading (one for each record). This has it's own overhead which is why ORM's are usually not a good idea for large scale inserting/updating data.

UPDATE 2
If you are running both the client and the database server on the exact same desktop machine then you are likely to have hardware issues. Hopefully you have a multi-core system with at least 4 GB of ram. Everything on your system is going to be competing for resources: visual studio, SQL Server, the app itself, plus whatever else you happen to be running. Studio and SQL Server are memory and CPU hogs. (FYI - SQL server doesn't report everything to task manager)

Point is, unless you are deploying the app with an embedded sql server this isn't going to come close to being a real world test and the performance you are seeing or lack thereof has no relationship to a real world scenario.