Estimate the Time for SQL query Execution

[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?

[Steve Walker]

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

[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?

[Victor Pereira]

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?

[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