Query execution time in Milisecond

Question

Query execution time in Milisecond

rahulpatel112

SSC-Addicted

Points: 419
More actions
August 1, 2013 at 1:02 am

#281304

Hi
I have one problem statement to create sq. query. For the solution i have created three different queries, one using CTE expression and two using sub queries. When i execute all three queries in SSMS on local database, takes zero seconds to execute.
I want to check performance of each query and want to find exact execution time in milliseconds. How can i check it.Is there any other way to find best query among all?
One way is to assign getdate() in datetime varibale before start of query execution and after the query execution complete and find the difference between both of them.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

...One way is to assign getdate() in datetime varibale before start of query execution and after the query execution complete and find the difference between both of them.

The above will be one of the precise methods to give you the answer.

Please note. If query you are testing return recordset, time taken to get data over to your client window will also be taken in count.

Also you can use SET STATISTICS TIME ON,

SQL Profiler and queries based on sys.dm_ views

Just remember, the time taken on your box is only one of many factors to judge upon performance.

Query plan would be the first thing to look into, checking what query is doing: scans or seeks, etc.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 2

I would suggest that you also use extended events to capture the execution of the queries. This will show the amount of CPU and I/O each query consumes. That will give you a very good indication of which one is better, in addition to run time. You don't want to rely on any single metric, run-time, i/o, cpu, but rather look at all of them.

And don't forget to check the execution plans. A CTE is nothing but a query, so you may find that the plans for those queries are identical, meaning they're all the same.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003896 More actions · Answer 3

Be careful about what you use SET STATISTICS on. If you have any scalar functions in the query, they can look hundreds of times worse than they are because of what SET STATISTICS does with them. Please see the following ariticle for more on that.

http://www.sqlservercentral.com/articles/T-SQL/91724/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

rahulpatel112 (8/1/2013)
One way is to assign getdate() in datetime varibale before start of query execution and after the query execution complete and find the difference between both of them.

That's almost exactly what I do when I measure performance, because in the end what counts is wallclock time.

Almost? Yes, I use datetime2(3) and sysdatetime() since that give millisecond resolution, and datetime only has a resolution of 3.33 ms.

As pointed out by others there are plenty of traps when measuring performance.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]