SQL query progress bar

[Tim K]

I've noticed the architect's SQL query tool has a progress bar during queries. Is there a way to get progress data during dc_AdsStatement :execute() ?

[rdonnay]

This requires a call back that cannot be written in Xbase++ but must be written in C.

I will look into this and see what can be done.

This is from the ADS documentation:

Callback Functionality with Queries

Providing progress information for a query is a complicated process. Queries are often non-deterministic in nature and calculating the length of time a query will take is often impossible until the query has completed, hardly useful by that point. Advantage’s query engine attempts to estimate how much of the query is complete throughout its execution. Optimizations within the query engine can lead to erratic progress information as short-circuiting eliminates unneeded processing. The 2-second pause between callbacks will smooth out most of this behavior, but slow progress followed by sudden completion, or the alternative, fast progress followed by relatively slow progress can still occur.

Callback functionality for queries will behave differently depending on the type of query and the resulting cursor type.

As far as progress functionality is concerned, there are 3 types of resulting cursors:

· Live cursors

· Static cursors that are not fully populated when the query is first executed

· Static cursors that are fully populated when the query is first executed

Queries that result in a live cursor are always fast except when they contain an ORDER BY clause. An ORDER BY clause can cause an index to be built on the resulting record set if no existing applicable index exists, and depending on the number of records in the resulting cursor, it could take a significant amount of time.

Advantage can optimize many static cursors by not fully populating them when the query is first executed. The server can often send records in a static cursor a few at a time to the client without having to populate the entire cursor, thus saving time and cutting down on network traffic. Should the client perform an operation that requires further populating the result set, it will let the server know and make a request for those records. This static cursor optimization means the original "execute SQL query" operation will return relatively quickly, and the last progress callback from the server (if there even was one) will report only a portion of the total progress as being complete. This is because populating the static cursor with records is the most expensive part of a query, and hence progress is tied mostly to the populating of the cursor. Record movement operations on the resulting dataset will cause further cursor population to occur, increasing the callback progress value until the entire cursor has been populated.

Some static cursors will be fully populated when the SQL query is first executed. These static cursors will report all their progress in the "execute SQL query" operation. Some static cursors are fully populated when the query is first executed because in order for the query to finish, the server must calculate every record that belongs in the cursor. This is usually associated with sorting the records within the cursor since the server cannot determine the final order until the cursor has been fully populated.

Progress information coupled with cancellation functionality allows one the ability to quickly recognize a query is taking an excessive amount of time, in which case it can be cancelled.

[Tim K]

Good information. Thank you.