I have been debugging some slow code and it seems that the culprit is the EF code posted below. It takes 4-5 seconds when the query is evaluated at a later stage. I'm trying to get it to run in under 1 second.
I have tested this using the SQL Server Profiler, and it seems that a bunch of SQL scripts are executed. It also confirms that it takes 3-4 seconds before SQL server is done with the executions.
I have read other similar questions about the use of Include() and it does seem that there is a performance penalty when using it. I've tried to split the below code into several different queries but it's not making much of difference.
Any idea how I can get the below to execute faster?
Currently the web app I'm working on is just showing an empty iframe while waiting for the below to complete. If I cannot get faster execution time I have to split it up and partially load the iframe with data or go with another asynchronous solution. Any ideas here would also be appreciated!
using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
formInstance = context.FormInstanceSet
.Includes(x => x.Include(fi => fi.FormDefinition).Include(fd => fd.FormSectionDefinitions).Include(fs => fs.FormStateDefinitionEditableSections))
.Includes(x => x.Include(fi => fi.FormDefinition).Include(fd => fd.FormStateDefinitions))
.Includes(x => x.Include(fi => fi.FormSectionInstances).Include(fs => fs.FormFieldInstances).Include(ff => ff.FormFieldDefinition).Include(ffd => ffd.FormFieldMetaDataDefinition).Include(ffmdd => ffmdd.ComplexTypePropertyNames))
.Include(x => x.CurrentFormStateInstance)
.Include(x => x.Files)
.FirstOrDefault(x => x.FormInstanceIdentifier == formInstanceIdentifier);
scope.Complete();
}
it does seem that there is a performance penalty when using Include
That's an understatement! Multiple Include
s quickly blow up the SQL query result both in width and in length. Why is that?
tl;dr Multiple Include
s blow up the SQL result set. Soon it becomes cheaper to load data by multiple database calls instead of running one mega statement. Try to find the best mixture of Include
and Load
statements.
Growth factor of Include
s
Let's say we have
- root entity
Root
- parent entity
Root.Parent
- child entities
Root.Children1
and Root.Children2
- a LINQ statement
Root.Include("Parent").Include("Children1").Include("Children2")
This builds a SQL statement that has the following structure:
SELECT *, <PseudoColumns>
FROM Root
JOIN Parent
JOIN Children1
UNION
SELECT *, <PseudoColumns>
FROM Root
JOIN Parent
JOIN Children2
These <PseudoColumns>
consist of expressions like CAST(NULL AS int) AS [C2],
and they serve to have the same amount of columns in all UNION
-ed queries. The first part adds pseudo columns for Child2
, the second part adds pseudo columns for Child1
.
This is what it means for the size of the SQL result set:
- Number of columns in the
SELECT
clause is the sum of all columns in the four tables
- The number of rows is the sum of records in included child collections
Since the total number of data points is columns * rows
, each additional Include
exponentially increases the total number of data points in the result set. Let me demonstrate that by taking Root
again, now with an additional Children3
collection. If all tables have 5 columns and 100 rows, we get:
One Include
(Root
+ 1 child collection): 10 columns * 100 rows = 1000 data points.
Two Include
s (Root
+ 2 child collections): 15 columns * 200 rows = 3000 data points.
Three Include
s (Root
+ 3 child collections): 20 columns * 300 rows = 6000 data points.
With 12 Includes
this would amount to 78000 data points!
Conversely, if you get all records for each table separately instead of 12 Includes
, you have 13 * 5 * 100
data points: 6500, less than 10%!
Now these numbers are somewhat exaggerated in that many of these data points will be null
, so they don't contribute much to the actual size of the result set that is sent to the client. But the query size and the task for the query optimizer certainly get affected negatively by increasing numbers of Include
s.
Balance
So using Includes
is a delicate balance between the cost of database calls and data volume. It's hard to give a rule of the thumb, but by now you can imagine that the data volume generally quickly outgrows the cost of extra calls if there are more than ~3 Includes
for child collections (but quite a bit more for parent Includes
, that only widen the result set).
Alternative
The alternative to Include
is to load data in separate queries:
context.Configuration.LazyLoadingEnabled = false;
var rootId = 1;
context.Children1.Where(c => c.RootId == rootId).Load();
context.Children2.Where(c => c.RootId == rootId).Load();
return context.Roots.Find(rootId);
This loads all required data into the context's cache. During this process, EF executes relationship fixup by which it auto-populates navigation properties (Root.Children
etc.) by loaded entities. The end result is identical to the statement with Include
s, except for one important difference: the child collections are not marked as loaded in the entity state manager, so EF will try to trigger lazy loading if you access them. That's why it's important to turn off lazy loading.
In reality, you will have to figure out which combination of Include
and Load
statements work best for you.
Do you have correctly configured relationships between all the entities that you try to 'include'? If at least one entity does not have a relationship to some of other entities, then EF will not able to construct one complex query using SQL join syntax - instead it will execute as many queries as many 'includes' you have. And of course, that will lead to performance issues. Could you please post the exact query(-es) that EF generates in order to get the data?