.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Articles >> ASP.NET >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Optimizing Performance

Posted By:Manning       Posted Date: February 19, 2011    Points: 75    Category: ASP.NET    URL: http://www.dotnetspark.com  

In This article author explain about the performance of Entity Framework, this segment explores four key areas where you can improve the speed and efficiency of Entity Framework.

This article is taken from the book Microsoft Entity Framework in Action. As part of a chapter on keeping an eye on the performance of Entity Framework, this segment explores four key areas where you can improve the speed and efficiency of Entity Framework.

40% off any version of Microsoft Entity Framework in Action with the checkout code dnspark40. Offer is only valid through www.manning.com.

You'll be glad to know that there are four areas where you can do a lot to improve the performance of Entity

Framework and in this section we are going to see them in detail:

f   Views pre-generation
f   LINQ to Entities queries compilation
f   Entity SQL plan caching
f   Change tracking

If you apply all the above optimization, you'll be surprised by how much Entity Framework gains upon
ADO.NET. Now, let's start from the first point which is dramatically important.

Pre-generating Views Views are Entity SQL statements that retrieve data from entity sets and association sets declared in the SSDL and CSDL. These commands are internally used by the Entity Client to generate the final SQL.

According  to  this  blog  post  from  the  ADO.NET  development  team http://blogs.msdn.com/adonet/archive/2008/02/04/exploring-the-performance-of-the-ado-net-entity-framework- part-1.aspx, generating views is the most expensive task in the query pipeline. It takes 56% of the entire process. Fortunately, views are generated once per AppDomain so once they are created the other queries can reuse them even if you use other contexts or the Entity Client.

As a result of view generation, the first query is tremendously slow. If I had warmed up the application, by issuing a dummy query, the result in table 1 would have been much different since views would have been already generated.
Table 1 shows the result after the warm up.

Table 1 Performance comparison between Entity Framework and ADO.NET to query customers starting with "C" after warmup.



171 ms

3.42 ms

LINQ to Entities
975 ms
19.5 ms
+470 %
Entity SQL via ObjectContext
788 ms
15.76 ms
+360 %
Entity SQL via Entity

416 ms
8.32 ms
+143 %

Everything is now faster. Entity Framework gains upon ADO.NET. Queries via Object Services benefit a lot from pre-generation, while queries issued using Entity Client perform basically the same as before. There's still something I don't like.
Issuing a dummy query works, but it's a waste. What's worse, when you deal with large models, Entity

Framework takes its time to generate views. Do you remember our beloved user? Do you think he would be happy to wait a long time before he starts to use the application? What you can do to avoid him yelling at you is
generating the views at design time, using EdmGen tool like in listing 1, and compile them into the project.

 Listing 1 Using EdmGen to generate views  

/mode:ViewGeneration "/inssdl:c:\OrderIT\model.ssdl" "/incsdl:c:\OrderIT\model.csdl" "/inmsl:c:\OrderIT\model.msl" "/outviews:c:\OrderIT\School.Views.cs"

EdmGen needs the CSDL, SSDL and MSL files and then returns a C# or VB file, depending on the /language
switch, containing the views. Now you just need to add the file to the project and compile everything.

The problem with this approach is that you need the three EDM files but you have only the edmx. You can let the designer generate the three files but then you should remember to reference them differently in the connection string and deploy them along with the rest of the application.

Another path you can follow is:

1. Set the designer to generate the three files on build

2. Build the application

3. Launch EdmGen in a post build event

4. Reset the designer to embed the files in the application

5. Build again

I hate this path even more than the one before. What I really want is a simpler way to generate the views at design time without touching the designer. Well the solution is astonishingly easy: template.

Generating views via template Although views generation happens internally, the APIs are public. It means that you can generate views from code. In other words you can write a template which reads the edmx extracts the three files and invokes the API to
generate the file.

 Listing 2 The main part of the template that generates view  

Using writer As New StreamWriter(New MemoryStream()) Dim csdlReader As XmlReader = Nothing
Dim mslReader As XmlReader = Nothing
Dim ssdlReader As XmlReader = Nothing

GetConceptualMappingAndStorageReaders(edmxFilePath, csdlReader,  #1 mslReader, ssdlReader)  #1
Dim edmItems As New EdmItemCollection(New XmlReader() {csdlReader})  #1
Dim storeItems As New StoreItemCollection(New XmlReader() {ssdlReader})#1
Dim mappingItems As New StorageMappingItemCollection(edmItems,  #1 storeItems, New XmlReader() {mslReader})  #1

Dim viewGenerator As New EntityViewGenerator()  #2 viewGenerator.LanguageOption = LanguageOption.GenerateVBCode  #2
Dim errors As IList(Of EdmSchemaError) =  #2 viewGenerator.GenerateViews(mappingItems, writer)  #2

For Each e As EdmSchemaError In errors  #3
Me.Error(e.Message)  #3
Next  #3

Dim memStream = TryCast(writer.BaseStream, MemoryStream)  #4
Me.WriteLine(Encoding.UTF8.GetString(memStream.ToArray())  #4
End Using

using (StreamWriter writer = new StreamWriter(new MemoryStream()))
XmlReader csdlReader = null; XmlReader mslReader = null; XmlReader ssdlReader = null;

GetConceptualMappingAndStorageReaders(edmxFilePath,  #1 out csdlReader, out mslReader, out ssdlReader);  #1 var edmItems = new EdmItemCollection(new XmlReader[] { csdlReader }); #1 var storeItems = new StoreItemCollection(new XmlReader[]{ssdlReader}); #1 var mappingItems = new StorageMappingItemCollection(edmItems,  #1 storeItems, new XmlReader[] { mslReader });  #1

EntityViewGenerator viewGenerator = new EntityViewGenerator();  #2 viewGenerator.LanguageOption = LanguageOption.GenerateCSharpCode;  #2
IList errors =  #2 viewGenerator.GenerateViews(mappingItems, writer);  #2

foreach (EdmSchemaError e in errors)  #3 this.Error(e.Message);  #3

MemoryStream memStream = writer.BaseStream as MemoryStream;  #4 this.WriteLine(Encoding.UTF8.GetString(memStream.ToArray());  #4

#1 Create EDM files from edmx
#2 Generate views code

#3 Enumerate errors
#4 Write views code in the file

In the first part, a stream for each edm file is extracted from edmx and the item collections are instantiated using them #1. Once it's done, the EntityViewGenerator class is instantiated and its GenerateView method is invoked passing the item collections, joined into one, and the writer the code will be written into #2. Eventually, any errors is sent to the output #3 and the stream containing the file is serialized as string and written to the output file #4.

Such template has already been created and made available for download by the Entity Framework team through  its  blog: 

I strongly recommend pre-generating views via template. You gain all the run time benefits of the already compiled views without any design time drawback. Yet pre-generation only speeds up things at start-up. Next we'll analyze a mechanism every LINQ to Entities query benefit from: query compilation.

Compiling LINQ to Entities queries LINQ to Entities queries are quite heavy to parse. Entity Framework team knows that and has added the possibility to compile them. By query compilation we tell the Object Services to parse a query and cache the generated command tree. The next time that query is executed the Object Services layer doesn't parse it again but uses the cached command. Obviously, the first time you execute that query, you get no benefits. They become evident from
the second execution on as you see in table 2.

Table 2 Performance comparison between Entity Framework and ADO.NET with and without LINQ to Entities query compilation.

First query
Other query average


4 ms

2,59 ms
LINQ to Entities (No compilation)
378 ms
12.38 ms
LINQ to Entities (compilation)
378 ms
10.01 ms

On the one side, compiling queries hugely helps in improving performance, but on the other side it requires a different style of coding. It means that during development you must immediately choose which queries must be compiled and which must not; introducing this feature in a second stage means changing a lot of code.

Writing a compiled query At first sight, creating a compiled query might look odd because it involves using the CompiledQuery class in association with a predicate. Anyway, after a bit of practice they'll look pretty familiar.
The first step is creating the static predicate. It must accept the context, a string containing the customer initials and must return an IQueryable. Inside it we place the query. Since we don't need instances of
the predicate, it's marked as static.

 Listing 3 The compiled query code  

Shared compQuery As Func(Of OrderITEntities, String, IQueryable(Of Customer)) =
CompiledQuery.Compile(Of OrderITEntities, String,
IQueryable(Of Customer)) (Function(ctx, name) ctx.Companies
.OfType(Of Customer)()

.Where(Function(c) c.Name.StartsWith(name)

static Func> compQuery = CompiledQuery.Compile>(
(ctx, name) => ctx.Companies
.Where(c => c.Name.StartsWith(name)

The first generic parameter of the predicate must be the context while the last one represents the return type. Between these two we can specify any parameter we need.
Now we need to use such query and it's easy as invoking the predicate passing in the context and the char "C".

Dim result = compQuery.Invoke(ctx, "C").ToList() 

var result = compQuery.Invoke(ctx, "C").ToList();

That's all you need to do to compile a LINQ to Entities query. As you see it's pretty simple. Object Services layer takes care of compiling query and reuse compiled version after the first execution. When the Object Services layer looks for an existing compiled query, it uses parameter too. It means that for if I search customers whose name starts with "C" and then other ones whose name starts with "A", Object Services caches two command tree.

Despite their simplicity, compiled queries have some nasty internals that are worth mentioning.

Compiled query internals The  first  thing  to  know  about  compiled queries  is  that  nothing happens until  they  are  actually executed. CompiledQuery.Invoke does nothing unless a ToList, ToArray, foreach or Execute forces queries to execute.
Another aspect of compiled queries is that if you combine them with other LINQ to Entities operator you lose
all benefits: the entire query is recompiled. For instance, if you use the query of the listing 19.8 and attach the
First operator, the entire command is reparsed and nothing is reused.

To mitigate the problem you can call the AsEnumerable method, which fetches from database and then use
LINQ to Object operators, or create a brand new compiled query.

Dim result = compQuery.Invoke(ctx, "C").First()  'complete parsing

var result = compQuery.Invoke(ctx, "C").First(); //complete parsing

Things get trickier when merge option comes into play. Merge option is set at query level. It means that when the query is compiled, merge option is saved along with the compiled version. Once it's created, you cannot change the merge option of a compiled query.

ctx1.Companies.MergeOption = MergeOption.NoTracking
var c = compiledQuery(ctx1, "C").AsEnumerable().First()  'detached

ctx2.Companies.MergeOption = MergeOption.AppendOnly
var c = compiledQuery(ctx2, "C").AsEnumerable().First()  'detached

ctx1.Companies.MergeOption = MergeOption.NoTracking;
var c = compiledQuery(ctx1, "C").AsEnumerable().First(); //detached

ctx2.Companies.MergeOption = MergeOption.AppendOnly;
var c = compiledQuery(ctx2, "C").AsEnumerable().First(); //detached

In the first case merge option is set to
NoTracking therefore the object is in Detached state. In the second case, even if the merge option has changed, the state of the object remains Detached because the query was

compiled with the NoTracking option. If you need to perform the same query with different merge options, I
recommend creating two separate predicates.
It's a really nasty behavior and I expect this to change in future versions. For the moment we just need to be aware of it so that we can avoid pitfalls. Next we'll look to improve performance with SQL plan caching.

Enable Plan Caching When you execute an Entity SQL query, the Entity Client parses it and then saves it in cache along with its counterpart in native SQL. The second time the query is executed, before it parses the query again, the Entity Client checks whether a copy in cache already exists.

The check is based on the Entity SQL string, query parameters and, most importantly, is case sensitive. "SELECT VALUE ..." is different from "Select Value ...". If you create two queries like those, you end up having two different entries for the same query and it's a waste of resources.

I strongly recommend deciding a convention to avoid that. My convention is that keywords must be upper case while objects name must use the Pascal annotation.
SELECT VALUE c FROM OrderITEntities.Companies AS c

This behavior is enabled by default so you have it for free. Should you need to disable it, you can do it via the boolean EnablePlanCaching property of the ObjectQuery and EntityCommand class.

Table 3 Entity SQL performance with plan caching enabled and disabled

Plan caching enabled
Plan caching disabled

Entity SQL via ObjectContext

9.66 ms

11.66 ms
Entity SQL via Entity Client
3.82 ms
4.84 ms

Table 19.5 highlights the fact that, from a performance point of view, leaving caching enabled is a good thing.
There's one last optimization to keep in mind and it regards change tracking. Many times you read objects but don't need to update them. Let's see how this scenario can be optimized

Disabling tracking when it's not needed When you show customers in a grid, you probably don't allow them to be modified. In such scenario change tracking is unnecessary therefore you can disable it simply setting the MergeOption of a query property to MergeOption.NoTracking.

This optimization lets object generation in the context skip many steps and has a dramatic impact on performance, as you can read in table 4.

Table 4 Performance with change tracking enabled and disabled (average)




2,59 ms

LINQ to Entities (with compilation)
10.21 ms
3.44 ms
+32 %
Entity SQL via ObjectContext
9.62 ms
3.33 ms
+28 %

As you see disabling change tracking makes object generation scream. After seeing this table you'll run to remove change tracking from your application wherever it's unnecessary.

We have already optimized Entity Framework considerably. For LINQ to Entities queries, we passed from a
+530% to a +32% compared with ADO.NET. This is why I always suggest checking not only SQL code but Entity Framework code too. I have encountered many cases where the problem wasn't the SQL produced by Entity Framework, but the total, sometimes partial, lack of code optimizations.

Another area where we can make a little optimization is in stored procedure execution. These types of queries
are interesting because they involve a different materialization process.

Stored procedure You know that invoking a stored procedure triggers a different materialization mechanism. Just to cover any case, I created a stored procedure that returns all order details and invoked it from Object Services, Entity Client and ADO.NET so that I could compare the results which are visible in table 5.

Table 5 Stored procedure execution performance



11 ms

0.22 ms

23 ms
0.46 ms
+109 %
Entity Client
19 ms
0.38 ms
+72 %

As you might have expected, ADO.NET is faster than any other technique. What's really interesting here is that Entity Framework is not so slower. If you compare this result with the one in table 1 you understand that the materialization mechanism used by stored procedure is much faster than the one used for queries.

Of course, here I have used another table which contains less data but that's not the point. What I really wanted to stress is the different materialization mechanism performance.

The methods added to the context for calling the stored procedures, hide the internal call to the context
ExecuteFunction method. This method has two overloads: the first one accepts the function name and the parameters and the second one accepts both plus the MergeOption. The methods added for each stored procedure internally use only the first overload so by default we have the AppendOnly behavior.

If we don't need change tracking for entities returned by the stored procedure, we can directly invoke the
ExecuteFunction method using the second overload passing the MergeOption.NoTracking value.

var parameters = CreateParameters();

var details = ctx.ExecuteFunction("GetDetails", MergeOption.NoTracking, parameters);

Even better, we could modify the template that generates the context to create two overloads for each stored procedure so that we don't have to manually invoke the ExecuteFunction method (The code that ships with Microsoft Entity Framework in Action already contains such template).

There's nothing more to add about performance. Now you know what the bottlenecks are, what you can do to avoid common pitfalls and, most importantly, what you can do to make database access via Entity Framework scream. It's not fast as ADO.NET, but it surely makes your life easier.

Stefano Mostarda, Marco De Sanctis, and Daniele BochicchioMEAP Release: February 2009Softbound print: December 2010 (est.) | 500 pagesISBN: 9781935182184

Microsoft Entity Framework in Action

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend