Calculating the estimated time of a sql query

# Thread: Calculating the estimated time of a sql query

1. Senior Member
Join Date
Dec 1969
Posts
119

## Calculating the estimated time of a sql query

Hi All,<BR> How can I calculate the estimated time to execute and reterive the result prior to execute the sql query with ASP?????<BR>Its Urgent please help.

2. Senior Member
Join Date
Dec 1969
Posts
1,435

## RE: Calculating the estimated time of a sql query

I don&#039t think it&#039s possible - a large number of things can affect how long a query takes to run, most notably the load your server is under.<BR><BR>I may be wrong - in fact I&#039d like to be proved wrong on this one!<BR><BR>Dunc

3. BalletChick Guest

## RE: Calculating the estimated time of a sql query

You can look into two things. First, the Query Analyzer in SQL server will help you determine which portions of your query take the most (and least!) time.<BR><BR>As far as determining the &#039actual time&#039 taken (and I assume this is because you want to forewarn you users of how long it will take?), you could simply time the duration and store that in a table somewhere. Obviously, the first time through, there will be no estimated time. But after the first time a particular query is run, (and assuming you timed it using the TIMER function), you could save that figure and show it to the next person who tries to run the query. You could also save this figure as a &#039moving average&#039 to smooth out the bumps in performance.<BR><BR>I dunno... it&#039s just a thought.

4. Senior Member
Join Date
Dec 1969
Posts
1,435

## RE: Calculating the estimated time of a sql query

Hey, yeah - that&#039s a pretty good idea! If you were to store a series of values it would also give you a very good idea of how your server performs under a variable load - you could see if a usage spike at a certain time of day is causing problems. I&#039d be a little worried about the extra load that this could generate, but it&#039s a very nice idea.<BR><BR>BTW - BalletChick, I&#039ve noticed you seem to post just about around the clock sometimes - don&#039t you ever sleep? ;-)<BR><BR>Cheers<BR><BR>Dunc

5. Senior Member
Join Date
Dec 1969
Posts
119

## RE: Calculating the estimated time of a sql query

If a query analizer can tell you the estimated time then why not ASP??<BR>I think we are missing some thing.<BR>

6. Senior Member
Join Date
Dec 1969
Posts
1,435

## RE: Calculating the estimated time of a sql query

Query Analyzer doesn&#039t tell you the *estimated* time to run a query, it just gives you the *actual* time for the query just executed. It can give you an estimated execution plan, but that&#039s a different thing... The problem is that the time taken to run a query can vary a lot depending on load.<BR><BR>Dunc

7. BalletChick Guest

## RE: Calculating the estimated time of a sql query

The query analyzer shows you what percentage of time for the total query was spent in each process (e.g. selection from this table, that table, merge, sort, etc.) so that you can optimize your query (i.e. add indexes, etc.)<BR><BR>

8. BalletChick Guest

## RE: Calculating the estimated time of a sql query

I work hard, and long hours. Do not take much sleep. :)

9. Junior Member
Join Date
Dec 1969
Posts
25

## RE: Calculating the estimated time of a sql query

use <BR>t1db = cdbl(timer)<BR> <BR> call getDataDB()<BR> <BR> t2db = cdbl(timer)<BR> tdb = cdbl(t2db - t1db)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•