Progress when getting database

[justme]

Hi all..
i want to show to user that my application is in process of getting data from database server (in %)
Can we use progressbar for showing it ?.. anybody know ?

 

[Geert Verhoeven]

Hi,

There is a process bar compoment in the toolbox. The problem is that in most cases you don't know the time in advance. Meaning that you must make an estimation to say at which time the progress bar is at what position.

Greetz,

Geert

 

[cjard]

The reason that you dont really see progressbars for this is because database query time falls into two sections:

Running the query - this is the potentially very slow part
Rettrieving the data - this is only really slow if downloading thousands of rows, which you should usually avoid

While running the query, the thread that called Execute on the database adapter, will block - it wont have any progress to report..


So, the essence of it is, that you cant easily report on the progress of an operation that is running on another process, maybe on another cpu in another machine.. To this end,seeking to give a progressbar to a query is largely pointless.

The best thing i;ve found to do in this situation is to keep a record of the run times of all the queries and when performing a long one, give a dialog to the user saying "This query on average takes X minutes to run (with the longest being Y. So far it has been running for Z minutes" and update Z with a timer.. The user can judge if the query is taking too long..

 

[o-mheien@hdsoftware.no]

Why? Number of records should do it

I havent done this in VB yet because I'm used to code in something called Clarion, but the sollution should be similar. Hers how I do it in Clarion, and then you should be able to do the same in VB.NET

MyView     VIEW
   Project(Customertable)
     Filter('CUST:LastVisited < ' & SomeDate)
   END!PROJECT
END VIEW
 
Open(MyView)
RecordsInView = Records(MyView)
Counter = 0
Set(MyView)
Loop until EOF(MyView)
  Next(MyView)
  Counter += 1
  Percent = int(Counter / RecordsInView * 100)
  ! Handle the record
End !Loop

Then I'm using the Percent value to update the progresbar. I'm sure you can do the same in VB

Regards
Ole

 

[Geert Verhoeven]

What we did for a project was to estimate the time needed. For example if a query requires 10 seconds we create a progress bar with a max of 30 seconds.

With a timer per second we then set the progress bar's value increesed with 1. The request to retrieve the data is done assynchronically. This way the UI is not blocked.

When 30 seconds have passed, a message is shown that there is something wrong with the database. When the result is available after a few seconds, we set the increment to 10 so the progress bar will fill faster.

Greetz,

Geert

 

[justme]

So..there is no way ?
Can we "catch" the "event" on database server (sqlserver) like "SQL server Profiler" ?...if we look on Profiler, we know what was going on SQLserver.

 

[Geert Verhoeven]

The problem is as mentioned before that your query is run at once.
You have 3 phases, the execute query phase, the create connection phase and the return results phase.

Either one of these phases have "milestones" to be able to increment the progressbar.

 

[cjard]

Then I'm using the Percent value to update the progresbar. I'm sure you can do the same in VB

Yes, but no.

If you have a query that takes 1 minute, and then returns 100 results, your progress bar will do nothing for a minute, then zoom to 100%

query = slow
download results=fast


progressbars are supposed to show progress, not hang for a long time, then zoom to 100% = pointless progress bar

 

[cjard]

So..there is no way ?
Can we "catch" the "event" on database server (sqlserver) like "SQL server Profiler" ?...if we look on Profiler, we know what was going on SQLserver.

Profiler times and counts all sorts of things, but it doesnt do it linearly! What do you want the database to do? After every row it has sucecssfully assembled, emit an event that another row is done? What if the database doesnt work like that? What if it doesnt even know how many rows will come out in the end? What if itself does a long operation that collates thousands of rows?

As I said before, give up on it.. You cant get the database to give you events useful for driving a progress bar to determine query progress.. If there was an economical way to do this, it would have been done by now..

The only thing you can do is do as I and Geert have said: provide the user with an estimate of how long to completion based on known past runs of the query

 

[justme]

ok i give up....
i just change mouse pointer to HourGlass...

 

[vis781]

Yick!!! Please do not change the mouse pointer to an hour glass, there is nothing more annoying in my opinion because it can mean any number of things and it's not just your app that can change it, the system does also when it's busy so you could be giving your clients a false interpretation of what is going on. Check out the links fom myself and JohnH in the following thread. Some good examples of different 'Never-Ending Progress Bars' Much more usful to the user and they look better too.

http://www.vbdotnetforums.com/showthread.php?t=6458

 

[cjard]

Never ending progress bars? I hate em about as much as hourglass pointers.. Recommend the "Inform user how long this normally takes, please wait" route.. Its amazing how long people will wait when they know how long they should wait.. Gotta admit though, a combination of those pretty bars and an expected wait time would make the app look sweet.. Nice find vis !

 

[vis781]

I agree, in this situation there just is no perfect way to explain to the user how long things are going to take. In this instance they are going to have to just do exactly what we tell them and wait whilst we show them funky graphics to try and ease their bordem. Virtually every 'User' i have met has the attention span of a supermarket chip and can't help tapping buttons or doing other daft things. Morale here... give them all the distraction and information that you can but always plan for the worst. They are all as thick as two short planks and if you don't write code with that in mind then you are heading for disaster. IMO

 

[justme]

Thanks Vis !
I'll use this...

 

[cjard]

So.. anyone got a DLL for tic tac toe, or battleships we can drop in for the users to play while they wait for the DB?

ps; to the OP, I forgot to say there is another route: optimise the SQL so the query doesnt take so long..