Tuesday, 16 February 2016

Detect slow queries in EF 6.x

Recently I came through a post from Rown miller as he created a simple interceptor to log poor performing queries or failing queries. Which really seems promising to track down those queries. Although there already an awesome well known tool Glimpse. This help you tracking down the server processing time and other informational data with very quick setup. It also log the queries if your Asp.net application is using EntityFramework. But it’s limited to Web applications. So what about Windows, WPF and other standalone apps?

I just extended the interceptor class as a library and included the support to introduce a custom logger to write queries on any target.
By introducing an Interface for logging:

/// <summary>
/// Implement this logger for any custom targets where queries should be logged.
/// </summary>
public interface IQueryLogger
{     void Write(params string[] content);

Checkout the original interceptor here. I have tweaked it a little to have filter for including StackTrace. Here’s the updated class.

let say I want to write to Visual studio Debug window, simply implement the IQueryLogger interface:

/// <summary>
/// Writes the output to the visual studio output window.
/// </summary>
public class OutputWindowLogger : IQueryLogger
{     public void Write(params string[] content)     
content.ToList().ForEach(data => System.Diagnostics.Trace.WriteLine(data, "Expensive Query log =>"));    
} }
A quick setup to use the interceptor is just implement the DbConfiguration class and add the interceptor:

public class CustomConfig : DbConfiguration
public CustomConfig()     {        
this.AddInterceptor(new ExpensiveSqlLoggerInterceptor(new OutputWindowLogger(), 1, false));    
} }

That’s it now you can run your application and look for debug window with “Expensive Query Log =>”

Here’s the complete source code of EF6Logger library on GitHub.