Timing Sql Queries with ADO.NET Entity Data Model

Question

NET Entity Data Model in an Asp.net MVC web application. The database is up in Azure. I am trying to time my Sql Queries like this with the start and end time either side of the query.

start = DateTime.Now;
var query = item.Database.SqlQuery<CustomerQuery>(queryString);
end = DateTime.Now;

I am calculating the time it took like this

Duration = end.Subtract(start);

Now no matter how many results are being returned, sometimes in the thousands the time is practically the same.

Why is this? and is there a way of getting a more accurate time

you might use `ToList()` or alike on the result to actually materialize the results (i.e. actually fetch them). Also, don't use DateTime.Now, use Stopwatch. — Christian.K, Jun 15 '16 at 10:33
If you want to time queries, use SQL Profiler. If you also want to time network delays etc, add Glimpse to your application and check the profiling information it collects for SQL access. Don't try to write this yourself, as in this case you have *two* bugs: First you don't execute the query, second you use an inaccurate method to calculate duration — Panagiotis Kanavos, Jun 15 '16 at 10:39

score 3 · Accepted Answer · edited Jun 15 '16 at 11:41

3

Well, I'm not expert in this stack, but seems that SqlQuery returns DbRawSqlQuery object, that encapsulates the query, but doesn't execute it. As MSDN says "A DbRawSqlQuery object that will execute the query when it is enumerated."

So you should materialize the query, for example by calling ToList().

var query = item.Database.SqlQuery<CustomerQuery>(queryString).ToList();

edited Jun 15 '16 at 11:41

Gert Arnold

105,341
31
202
291

answered Jun 15 '16 at 10:34

3615

3,787
3
20
35

Thanks @3615 this work perfectly now. I was Doing the query.ToList() when i was calling the view which was out of the start and end time – Confused Jun 15 '16 at 11:20

score 0 · Answer 2 · edited May 23 '17 at 10:29

0

The DateTime.Now method is not the best way to clocking milliseconds, you can read more about it in the answer from Jon here. You should instead use the Stopwatch class which can be used like this.

using System.Diagnostics;

In the method

var sw = new Stopwatch();
sw.Start();
var query = item.Database.SqlQuery<CustomerQuery>(queryString);
sw.Stop();

var totalTime = sw.ElapsedMilliseconds;

edited May 23 '17 at 10:29

Community

1
1

answered Jun 15 '16 at 10:33

Marcus Höglund

16,172
11
47
69

@GertArnold using DateTime to calculate durations can easily lead to this behaviour, if execution is fast enough. Stopwatch doesn't use clock time, instead it read a high-speed performance counter when starting and ending. The Ellapsed properties translate the difference to Ticks, milliseconds etc – Panagiotis Kanavos Jun 15 '16 at 10:37
Yes, but just saying *use Stopwatch* is not an answer. – Gert Arnold Jun 15 '16 at 10:39
@GertArnold You are right. I should have adressed the issue instead of just posting a solution. I will take the critic with me till my next answer here. – Marcus Höglund Jun 15 '16 at 10:44

score 0 · Answer 3 · answered Jun 15 '16 at 10:42

If you are using Azure, you might also be able to tap into the Application Insights. You can configure your application to send information up to Azure.

See

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-query-performance/ https://azure.microsoft.com/en-gb/documentation/articles/app-insights-asp-net/

Timing Sql Queries with ADO.NET Entity Data Model

3 Answers3