Countdown a Query Duration

[Mercadogs]

I would like to have Access show the user the wait time left for a query
to finish.

Most queries I've ran into in the past were rather fast (1 or 2 secs in
duration), however, my database now has some queries that can take up to 20
seconds to process. I want to have a pop-up of some sort that shows the wait
time remaining.

Is this even possible?

 

[Pat Hartman]

I don't think the actual time is computed. For long running queries, Access
displays a status bar in the Access window footer.

 

[Mercadogs]

I know that the actual time is not computed; that the status bar just shows
that the query is computing, but my question is: "Is there a way to show the
remaining computing time for the query that's being executed, not just the
status bar that says that it is computing?"

Thanks Pat. Does anybody else know?

 

[Jeff Boyce]

Let me try taking this a different direction...

"I have a query that takes 20 or more seconds to run. Any ideas on how to
speed it up?"

The first place I'd check is the fields used to select, sort or join. If
your query uses any of these, it may benefit from indexing being added to
the underlying table(s).

The next place I'd check is whether functions specific to Access/JET are
being applied to data coming from non-JET sources (e.g., SQL-Server
back-end). In this situation, Access pretty much has to download the entire
dataset before applying the (local) functions.

Finally, I'd look for subqueries embedded within my query, to see if there
are alternative ways to get the data.

If you'll post the SQL statement of your query, other readers may be able to
spot other efficiencies you could consider.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

By the way, "I know that the actual time is not computed" and "Is there a
way to show the remaining ... time" are mututally exclusive statements. I
don't see how you could have both <g>.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

What might help a little is to store the time the query takes and to show
that time indication when the user is running the query again.

john

 

[Mercadogs]

Thank Jeff. I will continue working on the efficiency of my query.
Nevertheless, my question is unanswered. I don't think I should post my
SQL here though. Thanks anyways, I'll just figure it out on my side.

 

[Mercadogs]

I think I like your suggestion. How do I accomplish this "[showing of] that
time indication when the user is running the query"?

 

[Guest]

You could create an 'control' table in which you store app related info,
like your app's path and settings. This table could have the following
fields with example:

ID_control Control_description Control_value
1 App Path C:\MyApp\

You can add a row like:
2 Running time Query1 100

When a user runs the query, you can dlookup the Query time of Query 1 and
show that in a field to the user telling him it's a time indication based on
the query's last running time. Then you run the query and you calculate the
time again and update the Control table (with an update query) with the new
calculated time which you use for the next time. This is just an example.
You can make the time indication more accurate by storing and using the
average of the running times of the 4 last queries and/or add a
number-of-records element in it. The number of records might increase your
particular query time.

John

I think I like your suggestion. How do I accomplish this "[showing of] that
time indication when the user is running the query"?

What might help a little is to store the time the query takes and to show
that time indication when the user is running the query again.

john