How to get round having a PC "freeze up" ?

[TheBionicOrange]

I'm sure I've read posts on this subject before, or at least something similar, but what I am looking for is suggestions on how best to tackle the problem.

We have a speed issue here, which is being addressed by tweaking a lot of the SQL statements so that as much processing as possible is done server side, whihc in my opinion should have been done at the very start but there you are.

The problem I/we have is that some statements can take 10 minutes plus to execute successfully, and I need some way of relaying to the user where the program is at.

As an example, we have 20 departments. Normally the easiest way would be to process a department at a time and update a panel in a status bar with which department is being processed. This way the user, knowing there are 20, can best guess how long something is going to take to complete.

Ideally, I want to process all 20 departments in one SQL statement, rather than 20 separate ones, as there are obvious performance gains to be made, and also less network traffic.

Can anyone come up with a suggestion as to how I might portray to the user that at least the program is running, rather than just changing the mousepointer to an hourglass ?

As it stands, the PC pretty much hangs until the SQL has finished, which is really less than ideal.

Any constructive feedback as always much appreciated ....

[si_the_geek]

One way to keep the form responding is to specify that the query should be run Asynchronously (if you are using 'rs.open', I think it is one of the values for Options). You can then have a 'wait' loop, which would be something like this:

Do While rs..StillExecuting
  DoEvents
  Sleep 200
Loop

You could also have some kind of indicator that work is being done.. perhaps an AVI file, or more simply a label with a changing caption (eg: / - \ | ).

[GettinBetter]

Not sure if this is feasible on your form, but the dual parallel progress bar type graphics, (there are many types) are a good idea, and very visual.
If space is at a premium they could be reduced to just two lines.

One ramps up fast, and the other (alot slower) is usually a fair representation of the overal timescale. The added tooltiptext would explain its function, to the user.

Just a thought.....

[TheBionicOrange]

Thanks guys. I'll try the asynchronous option first, although I have a feeling I have been down this road before.
Happy New Year by the way !

[schoolbusdriver]

Not sure if this is appropriate for what you're doing, but have you considered multithreading ? Example in post 10:- Re: SOLVED: Is simulated multithreading the way to go (for what i need to do)?