Estimate the Time for SQL query Execution

  • Thread starter Thread starter Saradhi
  • Start date Start date
S

Saradhi

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?
 
Saradhi said:
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?
Click to expand...

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?
 
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.
 
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?
 
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.
 
Back
Top