By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,432 Members | 790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,432 IT Pros & Developers. It's quick & easy.

Estimate the Time for SQL query Execution

P: n/a
Is there any way to estimate the time required to execute a T-SQL statement?
I need to set the command time out by calculating the time for T-SQL
statement. Can any one give an example?
Nov 17 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
In message <#c**************@TK2MSFTNGP12.phx.gbl>, Saradhi
<up*******@inooga.com> writes
Is there any way to estimate the time required to execute a T-SQL statement?
I need to set the command time out by calculating the time for T-SQL
statement. Can any one give an example?


You're probably better asking in somewhere like
microsoft.public.sqlserver.programming.

Having said that, it depends on too many factors for it to be a
particularly useful thing to do, in my opinion. What hardware will it
run on, how busy will the server be at the time, how many records will
there be in the tables, etc.

The decision on timeout has less to do with how long the command takes
to execute and more to do with your system requirements; how long is the
user willing to wait? Is it essential that the operation completes
however long it takes? Is running it asynchronously an option?

--
Steve Walker
Nov 17 '05 #2

P: n/a
Les
Saradhi,
I've seen a few tools that try to do just what you are asking, specifically
Oracle has a estimator and to be truthful, it is almost worthless if the
query is going to be of any significant complexity. You can kind of get a
rough idea of time for moderate queries, but large ones are very hit and miss
at best.

While I do not have an algo for your or other suggestions, I would take a
look at other paths, this one can really suck a lot of time and not give you
much return.

"Saradhi" wrote:
Is there any way to estimate the time required to execute a T-SQL statement?
I need to set the command time out by calculating the time for T-SQL
statement. Can any one give an example?

Nov 17 '05 #3

P: n/a
Why don't you test your query and see, Get the best time, the wrost time and
then get an average time ? Other important thing is how long is the user
willing to wait?
"Les" <Le*@discussions.microsoft.com> wrote in message
news:54**********************************@microsof t.com...
Saradhi,
I've seen a few tools that try to do just what you are asking, specifically Oracle has a estimator and to be truthful, it is almost worthless if the
query is going to be of any significant complexity. You can kind of get a
rough idea of time for moderate queries, but large ones are very hit and miss at best.

While I do not have an algo for your or other suggestions, I would take a
look at other paths, this one can really suck a lot of time and not give you much return.

"Saradhi" wrote:
Is there any way to estimate the time required to execute a T-SQL statement? I need to set the command time out by calculating the time for T-SQL
statement. Can any one give an example?

Nov 17 '05 #4

P: n/a
oj
Totally agree with Steve here. The decision for Timeout property should be
decided on the business/client need/expectation. Trying to dynamically set
this property is insane. Network congestion, server busy, etc can greatly
affect the final outcome.

In general, for simple *read-only* type query with a small set of data, 1
minute should be plenty. However, if you have some complex logic and/or
other external calls, you will have to jack the timeout up.

--
-oj
"Steve Walker" <st***@otolith.demon.co.uk> wrote in message
news:DW**************@otolith.demon.co.uk...
In message <#c**************@TK2MSFTNGP12.phx.gbl>, Saradhi
<up*******@inooga.com> writes
Is there any way to estimate the time required to execute a T-SQL
statement?
I need to set the command time out by calculating the time for T-SQL
statement. Can any one give an example?


You're probably better asking in somewhere like
microsoft.public.sqlserver.programming.

Having said that, it depends on too many factors for it to be a
particularly useful thing to do, in my opinion. What hardware will it run
on, how busy will the server be at the time, how many records will there
be in the tables, etc.

The decision on timeout has less to do with how long the command takes to
execute and more to do with your system requirements; how long is the user
willing to wait? Is it essential that the operation completes however long
it takes? Is running it asynchronously an option?

--
Steve Walker

Nov 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.