0

We are using entity framework 6.0 for development of our new application. All of our entity queries are generated from a DAL layer. For our current applications that are deployed to production, we use a SQL monitoring tool to track the performance of SQL queries.

My concern is how will I track down the DAL class that is generating the SQL so, I can address performance issues with the entity query. All I have from the tool is the SQL query that was generated by entity framework.

How are others tracking down SQL query issues in production? I know I can use Glimpse but how can you track back to the entity framework query that generated the SQL if you just have the raw SQL? I tried using the predicate builder to add a dummy where clause to see if that would show up in the SQL but it is ignored. like 

  predicate = predicate.Or(u => "methodName" == "methodName");

Thanks for the help.

Alexander
  • 3,129
  • 2
  • 19
  • 33
Mark
  • 53
  • 1
  • 3

2 Answers2

0

You could use New Relic to instrument the application and SQL. They have a feature called "Key Transactions" which can tell you the slow transactions (really set up just for web requests, but you can theoretically get it working for other types of apps) and allow you to see the slow SQL queries within those transactions.

In order to add your data access layer in to the methods being instrumented, you can edit the instrumentation xml files as per https://docs.newrelic.com/docs/dotnet/dotnet-agent-custom-metrics

Note that the Key Transactions feature is in the premium addition, which costs. You do get that free for a while, so it might be worth a look to see if it provides enough value to you.

(I have no affiliation with New Relic, by the way.)

Josh Gallagher
  • 5,211
  • 2
  • 33
  • 60
  • Thanks. But I am really interested in looking for a way to track down where the SQL is being generated from the source code. The only thing I will have is a performance profile on the SQL queries. When we find a query taking a long time we need to find out the entity query that generated the sql. I wonder what others are doing out there when they find poor performing queries. – Mark Feb 28 '14 at 01:08
  • I think it would be hard to reverse generate entity queries from the SQL in order to match them back, as there won't be a one to one mapping. E.g. string comparison can be done in multiple ways in the entity query that generate the same SQL. I would say most people would be using profilers on the code as well as the SQL as slow running queries will show up as slow running methods as well. – Josh Gallagher Feb 28 '14 at 10:15
  • Thanks Josh. Seems like I will have to spend sometime researching source code profilers I can run in production. – Mark Mar 02 '14 at 16:55
0

If you have a test suite covering the code that generates the queries, you could use this to save the generated SQL queries out to file along with the DAL method that generated them. You could do this by using the following code (taken from this SO answer regarding viewing the SQL):

var result = from x in appEntities
         where x.id = 32
         select x;

var sql = ((System.Data.Objects.ObjectQuery)result).ToTraceString();

If you saved these queries and method names in some structured fashion (e.g. CSV), perhaps as a side effect of the test run, you might be able to do a reverse lookup by searching this file for the query you see from production. You might have to do some normalisation, e.g. strip out all non-significant whitespace and in both cases take out the parameter assignments.

Community
  • 1
  • 1
Josh Gallagher
  • 5,211
  • 2
  • 33
  • 60