I teach a number of courses that include technologies that wrap up data access–everything from Entity Framework to WCF Data Services to RIA Services. They all simplify the business of working with data–but at the cost of hiding the implementation from the developer. One question that continually crops up in class is: how do I know what’s going on underneath?
There are a number of possible answers. You might, for example, be able to use the Visual Studio debugger to see the SQL statement has been issued–but in some cases there is no easy way to see exactly how your programmatic query has been translated into SQL. And that can be a real problem. You might be issuing massive queries when you only intended to select a small amount of data. And if you can’t see the SQL, you have no real way to know what’s actually going on.
That’s where a SQL Profiler comes in.
In a recent blog post, I talked about IQueryable and the Web API, and a reader posted a comment regarding the dangers of using oData for filtering–specifically, that all of the data would be pulled back into memory before being paged at the oData layer. This is just the kind of situation where a SQL Profiler helps you get to the bottom of what is actually happening: is the oData filter being incorporated into the query, or run against the return from the query? Let’s take a look and find out.
The profiler gives us a number of options:
You can check out performance:
Get some insight into processes:
Or you can trace individual SQL statements, which is what will allow us to determine how our query is being translated into SQL.
In this case, I’ve issued an oData query against the Web API and requested that only the top 5 elements are returned. In an ideal world, this would result in a SQL query for the TOP (5) matching rows.
As you can see, only five items were returned from the service. But where they filtered in the database or in memory after the whole data set was returned?
Let’s run a SQL trace and find out what’s really going on. I did so by clicking on “New SQL trace” and then accepting the defaults on the dialog:
Then I ran the query. The result was unequivocal: there is no ‘TOP’, so the entire data set is being returned and then filtered in memory:
If oData queries to the Web API behave that way, what of other frameworks that expose IQueryable? I quickly put together a RIA service against the Cocktails database and created a client-side query requesting the first 5 matching elements:
This time, the SQL profiler shows that the query has incorporated the paging restriction:
SQL profilers are indispensable for showing you what’s really going on in the database–good and bad.
For related information, check out these courses from Learning Tree: