timing queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

timing queries

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2008-02-12 : 04:51:26

How can I easily time a query entered in Studio Express for SQL Server 2005? Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 04:56:28

You want to get timetaken for a query?
Use SET STATISTICS TIME ON
or use GETDATE() to get starting & ending time and take difference starttime-endtime

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-12 : 05:20:43

you can also use profiler and look at the duration column.
how precise do you want it to be?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

RichardSteele
Posting Yak Master

160 Posts

Posted - 2008-02-12 : 10:05:04

It's a shared sql server that I don't have control over as administrator. I want to test a few queries for efficiency by typing the code into Studio Express. I was hoping for some sort of status line that would tell me the time the query took.

It sounds like both of the ideas put forth require admin access to sql server. Or perhaps not?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 10:12:10

Nope.not at all. just include them in your query

SET STATISTICS TIME ON

--your code here

or

DECLARE @StartTime datetime,@EndTime datetime

SELECT @StartTime=GETDATE()

--Your Code
SELECT @EndTime=GETDATE()

SELECT DATEDIFF(ms,@StartTime,@EndTime) AS ExecutionTime

RichardSteele
Posting Yak Master

160 Posts

Posted - 2008-02-12 : 10:28:14

Unfortunately neither the SET or DECLARE are supported in our shared sql server environment when entered in the sql pane. Any other ideas?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-12 : 10:30:00

what do you mean it's not supported? how can it be not supported??
are you sure you're using Microsoft SQL Server?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 10:43:29

He is using the query designer, not the query window.

Also, the profiler will give you much more time if you are using multiple processors.
Often I get 4 times as much time taken with profiler due to having 4 processors.

If code is taking 1.5 seconds, profiler says 6 seconds, which is correct in term of CPU time.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-12 : 11:03:41

for the duration column? didn't know that... interesting! so how do you know the acctual query time Peter?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 11:13:32

I mostly use the DATEDIFF(MILLISECOND, @StartTime, GETDATE()) approach, because this is the time taken experienced from client side.

E 12°55'05.25"
N 56°04'39.16"

RichardSteele
Posting Yak Master

160 Posts

Posted - 2008-02-12 : 11:14:45

Ok, I created a new query and I now see the execution time column.
It says 16 for this particular query. Is that in milliseconds?

RichardSteele
Posting Yak Master

160 Posts

Posted - 2008-02-12 : 11:16:00