Hello,

I have a select in a TEdbQuery that i cannot optimize more. I have an appropiate index for the select and the order. I have the OnStatusMessage event and the OnProgress event assigned to procedures that show a window to view the progress of the select when it is selecting records in the database. The result of this query is that the application hangs during 5 minutes without any event and after these 5 minutes, i begin to receive the OnProgress event with a percent of progress.

I thought that i had these events to give to the users a "progress" about how the application is selecting records in the tables of the databases, but it is not very useful to have to the user thinking that the "select" is hanged during 5 or more minutes ( it depends of the number of records to process ).

In my humble opinion, I have seen that in the unit edblocal.pas there are several procedures that could to be included in the OnStatusMessage processing. Why?, because the query could make the application to respond to events during all phase of it execution, and the final user of the application could see that the application is not hang.

These procedures :

TEDBFilterIndexNavigator.BuildOptimizedBitmap
TEDBFilterRowsetNavigator.BuildUnoptimizedBitmap

and any more procedures in the edblocal.pas unit, could to call to OnStatusMessage with messages like "scanning row xxxx", or any other message, in order to give the application a real progress of the execution of the query statement.

The Elevatedb creators should to think that it could to slow the query statement, but one solution could be to have a new event called for example : OnStatusMessageLowLevel, that the final user of the component could to use if he want to have a complete "progress" of the query execution phases.

Thank you and best regards.

A utility more of the above is that the query instruction may be canceled at any stage of query execution.

macc2010


This is something I had noticed in DBISAM and ElevateDB. Since I used SQL very little in DBISAM it didn't really hit me until I was doing some long operations in ElevateDB. From my memory of Tim's response the OnProgress event is only fired AFTER the WHERE clause has been evaluated so it refers to the building of the result set not the selection process (I could be wrong here but if so I'm sure someone will correct me)

The fundamental problem is that the best Tim could provide is some sort of heartbeat until he knows how to calculate percentages. For the average end user a message saying "now checking the index for table A" or "doing a row scan of condition X=Y" wouldn't be very friendly and would be more likely to cause confusion (or just be ignored).

What I chose to do was to ignore the OnProgress event entirely and have a something's happening indicator just so they know the process is still underway and the machine hasn't frozen. I just start it when I start the query and stop/hide it when the query's done.

Roy Lambert

Hello Roy,

Thanks for your reply.

I think that the solutions it's easier that to look for a system of partial progress that could take a lot of work to develop. In fact, you have said me that in DBISAM it never was solved despite the years that was developed

For example, in my case, all that i want is to have status messages that informs that the query is working, and to have the ability to cancel it. It could be responsability of the final user of the component to have de decision to show "ilogical messages", but usefull to cancel the query and not to have to be waiting to finish the intenal execution of index scan, row scan and internal things that the engine do before showing a progress. For example, if i consider that the messages are ilogicals, i could to translate them to "executing query", but i could have a way to cancel the query.

Do you mean that your solution is to have a timer for example?. The problem of this solution is that you cannot cancel the query, is not it?

Thank you and best regards.

Roy, one more think, the old and bad BDE has this solution when you register a callback for the TQuery handle. When the callback is called, the messages are like "10.000 records ordered", "12,000 records ordered" ... These messages, are "logical" and are sent to the callback every some time. The final result is that you can cancel the query execution, and there is an indicator viewing that the query is executing.

I think that a new event like OnStatusMessageLowLevel or the name that you want, could be a good solution to have a full control over all the execution phases of the query.

Thank you and best regards.

macc2010

>Roy, one more think, the old and bad BDE has this solution when you register a callback for the TQuery handle. When the callback is called, the messages are like "10.000 records ordered", "12,000 records ordered" ... These messages, are "logical" and are sent to the callback every some time. The final result is that you can cancel the query execution, and there is an indicator viewing that the query is executing.

How exactly did you do that because to the best of my memory the BDE never had any sort of progress event?

Roy Lambert

macc2010


I went back and found the original conversation I had with Tim - here's a couple of quotes

<<The progress is shown according to the navigation of the source tables, not
the application of the filters, etc.  If the time is being taken up by the
filtering, then that will not be reflected in the progress.  It's how you
can tell when your WHERE clause is un-optimized.>>

<<As soon as I can figure out a way to combine the progress of un-optimized
and optimized row and index scans into some semblance of a "total", and then
update against that in real-time, I'll certainly add it.  We're talking
about functionality that has no idea of what the "total amount of work to
do" is, so it's not a very simple matter to just put in some extra calls to
OnQueryProgress.  In many cases, the amount of work to do is highly
conditional and depends upon work that is already done (AND condition, for
example).  There are a lot of moving parts and it's not a simple matter of
(position/count)*100.>>

My favourite was

<<I find it interesting that it's been this way since DBISAM 1.x, and you're
just coming across it now. >

I had to agree but my explanation was I didn't use SQL much back then

Basically it boils down to the fact that even to provide a heartbeat and a cancel option the functionality would need to be built in to a large number of places in the code and that's not a simple or trivial task. The impact on performance would, I guess, be severe.

Going back to your original post I have a couple of thoughts:

If this query needs to be done in the foreground and is going to take 5 minutes and it actually needs to be done you do not want to give a typically impatient user the opportunity to cancel because all to often they will. Just give them something moving to watch whilst they twiddle their thumbs. I'll post the code for my distractor to the binaries.

If the query doesn't need to be carried out in the foreground move it into a background thread. This is what I do for certain classes of query in my app.

Finally, you say you have optimised the query. The gurus (not me) on this newsgroup have often proved me wrong when I've thought that. Why not post the query and execution plan for us to have a look at.


Roy Lambert

Roy Lambert wrote:

>How exactly did you do that because to the best of my memory the BDE never had any sort of progress event?

In dbtables.pas it is the class TBDECallback. In the Create constructor, the 6th parameter is the callback function to be called during sql progress execution. You can create a TBDECallback before open the query, and destroy it after closing the query and in the callback function you receive the progress.

macc2010

>>How exactly did you do that because to the best of my memory the BDE never had any sort of progress event?
>
>In dbtables.pas it is the class TBDECallback. In the Create constructor, the 6th parameter is the callback function to be called during sql progress execution. You can create a TBDECallback before open the query, and destroy it after closing the query and in the callback function you receive the progress.

Thanks for the info. Looking at examples I'm quite glad I never knew about it.

Roy Lambert

Roy Lambert wrote:

Thanks for the info. Looking at examples I'm quite glad I never knew about it.

<Hello Roy, nothing at all, thank you.

It is possible that this or any other query in my application has not the perfect index for the select, and i want that it be as now is designed, because, as you add more and more index to large tables, it is costly for all the systems to update the index when a record is added or modified. So I had a balance between performance and index limitations record saving costs to make the application quickly when entering records to the tables.

Also, as in a large application, I have some reports that the user does not use frequently, and in these cases, the index could not to be the appropiated for the select, and I would like to have a properly way to cancel the report as I had with BDE.

Thank you and best regards.