Posts Page - BizTalkGurus

Speaking at Victoria.Net – Tuesday 10th August 2010

by community-syndication | Aug 8, 2010 | BizTalk Community Blogs via Syndication

I will be presenting on:

Building Application that are Cluster Aware and testing them in a Virtual SQL Cluster with Hyper-V and Storage Server 2008

Today we are building more and more applications that run in a highly available environment, with SQL server this is typically a SQL Server cluster, but very few developers or developer organization have access to a SQL Server Cluster for development. Bill Chesnut will be talking about both the consideration you need to make in your code when connecting to a SQL Server Cluster and how to build a SQL Server Cluster with Microsoft Hyper-V server and Windows 2008 Storage Server and will demonstrate this environment running on a 8GB laptop.

Using StreamInsight to Monitor SQL Profile data for Events in SQL Server

by community-syndication | Aug 6, 2010 | BizTalk Community Blogs via Syndication

Nice project put together by one of our MVPs. He’s used the StreamInsight CEP tool to monitor data from SQL Profiler.

http://www.sqlis.com/sqlis/post/Streaming-Profiler-Data-Through-StreamInsight.aspx

Cheers

Peter

Using WF/WCF? Tell us about it!

by community-syndication | Aug 6, 2010 | BizTalk Community Blogs via Syndication

Are you using WF/WCF in .NET 4?

Would you like to help us improve WF/WCF in the next release?

Would you mind spending an hour or two in a conference call talking about what you like, don’t like and need help with?

We are looking for customers who would be willing to share their stories with us. If you want to participate in this research, please use the Email Blog Author link to let us know.

Potential Performance Issues with Compiled LINQ Query Re-Compiles

by community-syndication | Aug 6, 2010 | BizTalk Community Blogs via Syndication

When calling a query multiple times with Entity Framework the recommended approach is to use compiled LINQ queries. Compiling a query results in a performance hit the first time you use the query but subsequent calls execute much faster. The CompiledQuery class has functionality to compile and assist in caching the resulting database command from a query.

An example of a compiled query is show below. The query returns a list of Customers from the Northwinds sample database whose CustomerID starts with a specific string that is passed in as a parameter:

static Func<NorthwindEntities, string, IQueryable<Customer>> compiledCustQuery =
   CompiledQuery.Compile((NorthwindEntities ctx, string start) =>
   (from c in ctx.Customers
    where c.CustomerID.StartsWith(start)
    select c));

A few weeks ago while working with a partner on EF performance optimization, we ran into an interesting case where the EF code segment was executing much slower than expected. The Visual Studio Profiler showed that the code was spending an inordinate amount of time when executing a compiled query. Digging into the code showed that one particular operation on the compiled query was causing it to recompile every time it was executed. The code was checking to see if the result set returned any records. There are multiple ways to accomplish this. A common way is to use .Count()!=0 or Any(). In this case the compiled query returned an IQueryable. Calling Count() or Any() was causing the query to re-compile.

I wrote a small application to duplicate this issue using the Northwinds database. The code executes the compiled query to see if any records are returned. If there are records in the result set, it executes the query again and returns all the rows (the ToList().Count() makes sure all results from the second query are materialized in memory). Below is a snippet of code showing the two queries:

var qryAnyCust = compiledCustQuery(ctx, "C");

if (qryAnyCust.Any())
{
    var qryCust = compiledCustQuery(ctx, "C");
    qryCust.ToList().Count();
}

Running the Visual Studio profiler shows the following Hot Path:

Over 83.33% of the time is spent executing the System.Linq.Queryable.Any() class and only 16.67% of the time was spent actually executing the query. Executing the sample application yielded an execution time of approximately 9ms.

Next the code was modified to use a second version of the compiled query that contained the .Any() method in the query and returned a Boolean instead of an IQueryable. Below is the second query:

static Func<NorthwindEntities, string, bool> compiledAnyCustQuery =
   CompiledQuery.Compile((NorthwindEntities ctx, string start) =>
   (from c in ctx.Customers
    where c.CustomerID.StartsWith(start)
    select c).Any());

The query execution code was changed to call the above query to check if any records would be returned and then the query from the previous example to retrieve the actual records. This code is shown below:

if (compiledAnyCustQuery(ctx, "C"))
{
    var qryCust = compiledCustQuery(ctx, "C");
    qryCust.ToList().Count();
}

Looking at the profiler output of the above code shows that the call to Any() is no longer the bottleneck and that actually invoking the query now takes the majority of the time as expected:

Executing this code displayed much better performance. The test executed in only 6ms almost a 33% performance increase from a small change to a single query.

An alternative way to prevent re-compilation of the compiled query is to return an IEnumerable instead of IQueryable for the cached query. Using IEnumerable causes the query data to be returned from the server and then allows for evaluation and further refinement of the results set locally. For instance, executing a Count() on an IEnumerable from a query returns the number or records contained in the in-memory result set.

Note: Using IEnumerable will cause all of the query results data to be transferred from the server to the client. This can be a drain on memory and performance on the client if the query returns a large number of records. This might not be the best approach if you have large result sets and simply need to get record counts.

Below is the code from the sample application which returns the compiled query as an IEnumerable instead of IQueryable:

static Func<NorthwindEntities, string, IEnumerable<Customer>> compiledEnumCustQuery =
   CompiledQuery.Compile((NorthwindEntities ctx, string start) =>
   (from c in ctx.Customers
    where c.CustomerID.StartsWith(start)
    select c));

A third version of the query execution, is show below, that checks to see if any records would be returned and then the query from the previous example to retrieve the actual records:

if (qryEnumAnyCust.Any())
{
    var qryCust = compiledCustQuery(ctx, "C");
    qryCust.ToList().Count();
}

Executing the above code displayed similar performance to the previous test that used the compiled queries containing the Any(). The test executed in only 6.7ms which was much better than the original code. Profiling the IEnumerable test shows that now most of the execution time is spent in the Any() method on the IEnumerable which should execute quickly because the values are in memory:

In the case of our partner, changing their code to use compiled queries to contain the Any() and return a Boolean boosted their performance 450% because their code checked if queries returned results in multiple places.

Query recompile is not limited to Any() or Count() operations. It can happen with any LINQ operator that is applied on top of a compiled query. So be on the lookout for query re-compilation in your code. It can unknowingly impact performance. In a future post I will cover in more detail the use the Visual Studio Profiler to uncover bottlenecks in the Entity Framework.

Author: Dana Kaufman
Reviewers: Jaime Alva Bravo, Daniel Simmons, Diego Vega, Peter Scharlock

Profiling Entity Framework using the Visual Studio 2010 Profiler

by community-syndication | Aug 6, 2010 | BizTalk Community Blogs via Syndication

A previous post discussed potential issues with Compiled Query recompiles. The cause of the recompile issues were actually uncovered by using the Visual Studio 2010 Profiler. This blog will cover how the Profiler can be used to discover performance issues in the Entity Framework. The sample code from the previous blog will be used as the code to be profiled.

Basically the code executes a compiled query against the Northwind database via the Entity Framework. The LINQ query (using LINQ to Entity) returns all Customers whose CustomerID starts with “C”. The code first executes the query using a .Any() operator to see if there will be any records returned and if so executes the query and returns a list of records which we then take the count of to make sure all of the records are inspected. The queries are executed 100 times. The sample is in a solution named EFTest which you can download here.

Executing this class returns the following results:

Notice the first query took 1144ms which is significantly longer than the follow on queries. This is due to the warm-up that happens in the Entity Framework when an application starts. The first time an ObjectContext is used, EF opens up a connection to SQL Server and loads up the metadata for the EF model. This metadata includes the mappings between the physical and conceptual models and any pre-compiled view SQL.

Now let’s dig into the performance of the LINQ queries. The Visual Studio Profiler can be used to isolate potential performance issues. The Profiler can be enabled by selecting “Analyze | Launch Performance Wizard…” from the Visual Studio main menu. This launches the Performance Wizard which is shown below:

Take the default and hit “Next”, take the default for the target application, press “Next” and then uncheck the option “Launch profiling after the wizard finishes”. Now press “Finish” which will close the wizard.

The Performance Explorer should be shown in Visual Studio:

Right click on the EFTestAny project in the Performance Explorer and select “Properties”. Select “Sampling” from the properties list and change the Sampling interval to “50000”. This will make the profile take executions samples at a faster rate which increases accuracy when profiling such a small amount of code. Click “Ok” to close the property page.

Before we start the profiling, one more task needs to be completed. Remember the first query took significantly longer than any of the subsequent queries due to opening the connection to SQL Server and EF metadata loading? We need to factor the warm-up out of the performance measurements. This can be done by making marks in the data collected by the profiler and then filtering the results on those marks. The Profiler’s API can be used to insert Data marks via code.

To use the Profiler API, a Profiler assembly needs to be added as a reference to the project. Right click on “References” in the Solution Explorer and pick “Add Reference…”. Use the Browse tab and select c:\Program Files\Microsoft Visual Studio 10.0\Team Tools\Performance Tools\Microsoft.VisualStudio.Profiler.dll

Next the DataCollection.CommentMarkProfile() method can be used from the code. This method adds a numeric mark and comment into the performance data being collected by the profiler. The method is passed a MarkId which is an integer and helps in ordering the marks and a description string. The CommentMarkProfile() call needs to happen after the first round of queries to get past the warm-up so that the cost can be excluded from the analysis. To do that, the following code is added after the results are printed for each pass (note the if statement makes sure the mark only happens after the first iteration):

if (i == 0) //Mark profile data after first pass as it is warmup
{
    //Mark profiling data
    DataCollection.CommentMarkProfile(100, "After Warmup - Any ON compiled query");
}

Finally, the CommentMarkProfile() operation is called after the for loop completes before the code pauses for a key press. The code for the second mark is shown below:

DataCollection.CommentMarkProfile(110, "Queries Done");

The complete code with the calls to the profiler API is shown below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Data.Common;
using System.Data.EntityClient;
using System.Data;
using Microsoft.VisualStudio.Profiler;
  
namespace EFAnyTest
{
    class Program
    {
  
        static Func<NorthwindEntities, string, IQueryable<Customer>> compiledCustQuery =
           CompiledQuery.Compile((NorthwindEntities ctx, string start) =>
           (from c in ctx.Customers
            where c.CustomerID.StartsWith(start)
            select c));
  
        static void Main(string[] args)
        {
            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            double totalTime = 0.0;
            int iterations = 0;
            int TEST_RUNS = 100;
  
            NorthwindEntities ctx = new NorthwindEntities();
            {
                Console.Out.WriteLine("Any ON compiled query");
                for (int i = 0; i < TEST_RUNS; i++)
                {
                    sw.Start();
                    var qryAnyCust = compiledCustQuery(ctx, "C");
 
                    if (qryAnyCust.Any())
                    {
                        var qryCust = compiledCustQuery(ctx, "C");
                        Console.Out.Write("{0} Cust records, ",qryCust.ToList().Count());
                    }
                    sw.Stop();
                    totalTime += sw.Elapsed.TotalMilliseconds;
                    iterations++;
                    Console.Out.WriteLine("iteration = {0} Last:{1}ms, Average:{2}ms", i, sw.Elapsed.TotalMilliseconds, (totalTime / (double)iterations));
                    if (i == 0) //Mark profile data after first pass as it is warmup
                    {
                        //Mark profiling data
                        DataCollection.CommentMarkProfile(100, "After Warmup - Any ON compiled query");
                    }
                    sw.Reset();
                }
                Console.Out.WriteLine("");
            }
            DataCollection.CommentMarkProfile(110, "Queries Done");
            Console.WriteLine("Press and key to continue....");
            Console.Read();
        }
    }
}

Now it’s time to profile the application. In the Performance Explorer, click on the icon for “Launch with Profiling”.

Note: If you see an error message about not being able to open the profiler driver, select “Yes” to upgrade the credential of the current user.

The application will execute and then press the <Enter> key to close the application. Visual Studio will churn for a few minutes and then show the Profile report, but were not ready to study it just yet. These results still contain the warm-up code profile data which includes the loading of the metadata and connecting to the SQL server. To look only at the LINQ query execution, change the “Current View” of the report to “Marks” via the drop down list. A list of profile data marks will be shown including the ones that were set in the code. Highlight all of the marks between the two custom code marks (100 and 110), right-click and select “Add Filter on Marks”:

Now press the “Execute Filter” button which is the second one to the right of the Current View dropdown list. This will filter the profile data and only include data between the marks we inserted via code and then it will change the current view back to the Summary view. Scrolling down on the refreshed report shows the code HotPath(the section of the code that took the majority of the time executing):

As shown above, the call to System.Linq.Queryable.Any() is shown as the most expensive call. This call should be fast because it should simply check if any records are return in the result set so we need to drill in further to find the root cause.

To do this an additional option needs to be changed. Select “Debug | Options and Settings…” from the Visual Studio main menu. Click on “Performance Tools” and uncheck the “Enable Just My Code for profiler reports (sampling)” in the “Just My Code” section and press the “Ok” button. Now re-run the profiler to generate a second report and then filter that report on our code marks as we did above.

Now the report shows a lot more detail. The System.Data.Query.PlanCompiler class is being called even though the query should have already been compiled the first time we accessed it during our warm-up pass. As discussed in the previous blog entry, the call to the Any() method on the IQueriable is causing the query to re-compile each time which is an expensive operation.

CONCLUSION

As you can see, the Visual Studio 2010 Profiler can be a powerful tool to track down potential bottlenecks in your Entity Framework code or other parts of your application. And using the Profiler APIs, provides an easy way to hone in on performance characteristics of specific parts of an application. So go forth and profile that code, you never know what you will uncover.

Author: Dana Kaufman
Reviewers: Jaime Alva Bravo, Daniel Simmons, Paolo Salvatori

Epicor Visual Studio 2010 Entity Framework Case Study Posted

by community-syndication | Aug 6, 2010 | BizTalk Community Blogs via Syndication

Check out the Epicor Case Study that was recently published by one of our partners. Epicor is a global software company that creates enterprise resource planning (ERP) solutions for manufacturing firms and other customers.

They have been able to leverage Visual Studio 2010 and ADO.NET Entity Framework 4 to ease development and reduce SQL Server requests by 90 percent. Also, Epicor has a very large data model which the new version of Entity Framework was able to support.

An excerpt from the case study:

Because the ADO.NET Entity Framework 4 can support large data model sizes, Epicor could use the technology to create Epicor 9.05. “The earlier version of the Entity Framework did not handle our data model size very well, and we experienced delays in testing,” says Johnson. “Those problems have all been solved with the ADO.NET Entity Framework 4.

Authored by: Dana Kaufman

Isolating Performance with Precompiled/Pre-generated Views in the Entity Framework 4

by community-syndication | Aug 6, 2010 | BizTalk Community Blogs via Syndication

Introduction

Working with customer’s solutions, we often have to track down potential performance issues or uncover areas for improvement. One of the techniques is to try and measure the relative performance gained using the different configurations or features (likely in a staging/testing environment). When working with Entity Framework 4 one of the potential optimizations is to use precompiled views where Entity Framework query views are pre-built and compiled into the application; which otherwise, the entity framework would have to generate on the fly (extra resources being required). The following are some useful references on precompiled views:

· MSDN – How to: Pre-Generate Views to Improve Query Performance (Entity Framework)

· EF Team Blog – How to use a T4 template for View Generation

This sample application shows one possible way to test potential performance gains and resource utilization when using pre-generated views by providing a performance comparison between runs with precompiled views and without. You can download the sample code here.

When isolating performance, a good idea is to focus on a very narrow piece, preferable a section that gets the most use. Using Visual Studio Profiler to find the bottleneck can point to the slowest query. Then by leveraging the analysis techniques discussed below, the performance gains can be observed/studied.

Note: that the sample for this article leverages the T4 template for View Generation mentioned in the EF Team Blog link above.

Analyzing the Sample

The database – The complete sample code (solution named: PerfSample), executes a query against a local database named PerfSample. To create it, the 2 SQL scripts found at the root of the downloadable ZIP file need to be executed against an empty Database. Open the first one, named EFModelGen.SQL, with Microsoft SQL Server Management Studio and Execute it. This will not create the entities yet but instead, it will generate the required SQL script. Copy and paste the results into a new query and run it against your empty database, this will create 200 empty entities. To create records for the entities Type1 and Type2 run the second script, named PopulateData.sql. It contains 2 variables: Type1RecordCount and oneToMany. The first indicates the number of records to be created in entity Type1, and since entities Type1 and Type2 have one-to-many relationship, the second variable indicates how many Type2 records will be created per each Type1 record.

The sample executes a LINQ select statement with a join against the Type1 and Type2 entities. For the purpose of testing precompiled views, the results need to be materialized (turning data records into “real” objects) hence, the sample runs ToList().Count() against the query results, the simple code is as follows.

   var type1WithType2 = from t1 in context.Type1
                        join t2 in context.Type2
                        on t1.Type1Id equals t2.Type1_Type1Id
                           select new
                           { t1.Type1_Col30, t2.Type2_Col30 };
   type1WithType2.ToList().Count();

Once the query is identified, execution time (performance) and memory usage (resource) is recorded. To measure time, the StopWatch class is used. For recording memory usage, a memory reading is collected into long integers before and after the query is executed and then the numbers are subtracted, which will provide only the memory used by the query. To look at Managed memory (memory used only by the framework), first the garbage collector (GC) is forced to run, that way obtaining a clearer read of the memory being used by the framework. Note that this is purposely done before the stopwatch starts since the GetTotalMemory (true) method may take a little while to return while the GC is run (which can be unpredictably long or short).

   startManageMemoryBytes = GC.GetTotalMemory(true); //Force GC
   stopWatch.Start();

      //[Query/code to analyze]

   stopWatch.Stop();
   endManageMemoryBytes = GC.GetTotalMemory(false);

The program is compiled with the Visual Studio and to streamline things a bit more, it is built in Release mode, the SQL server is local to the machine (minimizing network delays) and its affinity set to CPU1 (minimizing CPU context switching between SQL and the application), tests were executed on a dual-proc machine.

The query above is executed 3 times (call to QueryToTest(context)), in order to collect more even results.

Performing the tests

The sample has to first be prepared to either run in precompiled mode or in standard mode. To setup a precompiled run, make sure the PerfSample.view.tt is included in the solution and that it has a corresponding PerfSample.views.cs file (if the .cs file is missing then it can be generated by doing a right-click on the T4 template PerfSample.view.tt and choosing ‘Run Custom Tool’) and then clean and rebuilding the solution. Likewise, to setup a run without precompiled views, exclude the T4 template PerfSample.view.tt from the project (right-click and choose to ‘Exclude From Project’) then clean and rebuild the solution.

The sample program is a simple console application that is executed via a small DOS batch file. The batch file runs the program 3 times and sends the info to the console; it is named runtest.bat and is found under …/bin/release directory. To make it easier to analyze, the results are piped to a text file in the same directory.

The following DOS commands are executed to collect the respective results, after the proper changes are made (as explained above)

   Runtests > WithPreCompiledViews.txt
   Runtests > WithoutPreCompiledViews.txt

NOTE: To append results into an existing file, replace the single less-than symbol (“>”) with the double pipe command “>>”

The test batch file is run multiple times to get a good sample of performance times.

The Results

without precompiled views

with precompiled views