bb.donnay-software.com

Donnay Software Web Forums
It is currently Wed May 27, 2020 8:37 pm

All times are UTC - 7 hours




Post new topic Reply to topic  [ 3 posts ] 
Author Message
 Post subject: SQL query progress bar
PostPosted: Mon Jun 10, 2019 3:45 pm 
Offline
User avatar

Joined: Thu Jun 03, 2010 3:55 pm
Posts: 51
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() ?


Top
 Profile  
 
PostPosted: Tue Jun 11, 2019 5:29 am 
Offline
Site Admin
User avatar

Joined: Wed Jan 27, 2010 6:58 pm
Posts: 4124
Location: Boise, Idaho USA
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.

_________________
The eXpress train is coming - and it has more cars.


Top
 Profile  
 
PostPosted: Tue Jun 11, 2019 9:21 am 
Offline
User avatar

Joined: Thu Jun 03, 2010 3:55 pm
Posts: 51
Good information. Thank you.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 7 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group