Step 1 - get an explain plan, in particular EXPLAIN EXTENDED will show you what SQL the query optimizer actually generated. Maybe additional indexes will help - but if you "outsource" your SQL generation then you really are at the mercy of whatever was easier for the ORM developer, not what was best for your application. Another thing you might try is in-memory temp db, as this can help with sorts, and derived tables, which you do have...
Individual queries are ok
One of the biggest performance 'issues' with ORM tools (Entity Framework, Linq, LLBLGen, NHibernate, etc...) is not so much the performance of the individual queries that are executed (most are just CRUD calls which are retrieving a single record back based on a primary key).
Incorrect use of Lazy Loading = Bad Performance
It is the incorrect use of lazy loading, where if you have not setup your prefetch attributes correctly you can end up with a significant number of executes against the database (something that could have been retrieved in a single call to the database, becomes 200 individual executes, with all of the network and IO overhead associated with them)
from LINQ to SQL, Lazy Loading and Prefetching
It turns out that each access to the
ProjectEntity and CustomerEntity
object causes a separate query to the
server (shown) for the first time a
particular Project or Entity is
referenced. This means for each
project in the application there will
be two additional queries hitting the
server so if I show 20 projects I’ll
hit the database 40 extra times.
That’s lazy loading for you and it’s
usually a problem in list based
displays like this. It can be much
worse if EVERY row you display
requires one or more related entities.
Key is knowing what you're accessing
The real difference between using an ORM and handcranking it yourself is that when you code it yourself you are forced to think about how the SQL should be structured and you can easily go in and tweak it for the particular scenario you're trying to solve.
Whereas when using an ORM you are limited by the options and customization that the ORM tool gives you, and a lot of developers just leave it to the ORM tool itself (assuming/hoping it will come up with best plan), which is why ORM tools are widely regarded as being good for CRUD but not for more complex operations...
Note: Lazy loading can be a really good thing, it's when it's used inappropriately that's the issue...
Best Answer
I created a small test application to see if there were any issues accessing IMOLTP tables from Entity Framework. My test application uses the latest stable version of Entity Framework (EF6).
With the default settings, I got this error initially trying to insert a row into a memory-optimized table:
This is because Entity Framework creates an explicit transaction automatically when using the "SaveChanges()" method (the most common approach for inserting rows):
The simplest way to work around this in the case of EF is to turn on the database-level option
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, which essentially adds the neededWITH (SNAPSHOT)hint automatically:Other than that, there seems to be no issues with accessing the in-memory tables. This isn't all that surprising, since under the covers Entity Framework is using the .NET Framework Data Provider for SQL Server (SqlClient), which has no documented limitations when it comes to in-memory tables.