question

Tariq Quadri avatar image
Tariq Quadri asked

How to get the time required to retreive data using a SELECT query in SQL ?

How to get the time required to retreive data using a SELECT query in SQL ? I want to get the time that was required to retrieve data which might be more than one record using sql SELECT query ?

sql-server-2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TG avatar image
TG answered

Well if you mean the time it took a statement to run a couple ways from a SSMS query window are to:

set statistics time on            

Then run your statement. Another way is to:

declare @s datetime            
set @s = getdate()            
<your statement>            
select datediff(millisecond, @s, getdate()) as durationMS            

Now if you mean you want to anticipate how long a statement will take without having run it, that would be much more difficult. At best you could only take an educated guess. But even then current server conditions could prevent an accurate guess.

For "end-to-end" times from an application then you would need to perform a programmatic measure similar to my second example but from your application code

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

For a .NET app, the timing code would look like this:

Stopwatch sw = new Stopwatch();            
sw.Start();            
... code ...            
int timeElapsed = sw.ElapsedMilliseconds;            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.