Using asynchronous SQLEXEC() still freezes the UI

[DirkVFP]

Hello all,

I have a query which takes some time to complete. During the execution of the query the screen freezes and I want to prevent this by using asynchronous execution of the query.
I've searched the forum and I've produced some code following the example from Mike Lewis (link:

http://www.tek-tips.com/viewthread.cfm?qid=801062).

Here's the code:

LOCAL lnConn,lcQuery,lnRes

*** Setting up the connection.
lnConn = SQLCONNECT("dsn_name","login","pass")
SQLSETPROP(lnConn,"Asynchronous",.t.)

*** The query which will take some time to complete.
lcQuery = "select * from big_table"

*** Execute this query asynchronously so the user interface won't freeze up.
DO WHILE .t. 
	lnRes = SQLEXEC(pnHandle,lcQuery,"tresult")
	DO CASE 
		CASE lnRes = 0 
			Thisform.lblMessage.Caption = "Busy..."
		CASE lnRes > 0
			Thisform.lblMessage.Caption = "Ready!"
			EXIT 
		CASE lnRes < 0
			Thisform.lblMessage.Caption = "An error has occured!"
			EXIT
	ENDCASE
	DOEVENTS
ENDDO

I've added DOEVENTS but this also does not help; the UI keeps on freezing up during query execution. I'm using PostgreSQL as a back-end, could the problem lie in my ODBC driver or am I doing something wrong here?

Thanks in advance,
Dirk

 

[Mike Lewis]

Dirk,

Asynchronous SQL pass-through isn't really intended to free up the user interface. Even if you put DO EVENTS in the loop, most of the time the application will be processing the code in the loop, and won't allow any time for the user to interact.

The reason to use asynchronous SQL pass-through is to let your application do something useful in the background. You would normally write code in the "CASE lnRes = 0" branch to perform some useful action, like updating a progress bar.

It's true that you can respond to events at that point, but this would be extremely slow, and would probably give the impression that the application has hung. Just about the only useful thing you can do in that respect is to monitor a single mouse click or keystroke, for example, to let the user signal that they want to cancel the query.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[DirkVFP]

The reason to use asynchronous SQL pass-through is to let your application do something useful in the background. You would normally write code in the "CASE lnRes = 0" branch to perform some useful action, like updating a progress bar.

Thanks for clearing this out Mike.

However I'm still stuck with a UI freezing up. Is there any common practice to counter such behaviour?

Dirk

 

[Mike Lewis]

Dirk,

However I'm still stuck with a UI freezing up. Is there any common practice to counter such behaviour?

No, you can't prevent the "freezing". In general (and this has got nothing to do with SQL pass-through), an application won't process user events while a process is running. The best thing you can do is to make it obvious to the user that they have to wait. The standard way to do that is to display an hourglass as the mouse pointer.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[DirkVFP]

Mike,

Thanks again for the quick response.
I was hoping VFP offered something like asynchronous programming used in .NET (or even use different threads),
but since that is not the case I will follow your advice.

Dirk

 

[rkolva]

I have a similar need to parse out some SQL to the background and leave the GUI responsive to the user. My initial ideas are to either use Calvin Hsia's 'Multithread My VFP' (

http://blogs.msdn.com/calvin_hsia/archive/2006/05/16/599108.aspx)

or to strip out the SQL into a seperate Exe so it runs in it's own thread.

I would be interested in this groups experiences and opinions on doing this.

Ralph Kolva

 

[Imaginecorp]

Calvin in IMO is brilliant. Claude Fox has redone this to make it more efficient and removed some minor bugs. But as Calvin himself states

...On my dual processor machine (with hyperthreading on), with 10 threads, the performance was almost double the performance of a single thread, as expected. On my single processor laptop, the performance is roughly the same, with the single threaded slightly faster than the multithreaded, due to thread overhead...

You may be solving the asynchronous problem, but you will be slowing the machine down sometimes considerably...unless your users are on multi processor machines.

Also this is asking VFP to do what it was never designed for ... Multi Thread. You may be opening up a pandora box.

There are other and more efficient ways to run a query, especially when accessing large amounts of data.

One possibility is using a compiled remote view and fetching 100 records at a time. There is hardly a wait for this to execute and if your user wants to see the next 100.. he can, at least he is involved and is not sitting around twiddling his thumbs. If he does not he can Cancel.

Another, if this query is an integral part of the app and is run with minor modifications, create summary tables of the often used criteria.

If a user, and there is always one, wants to run the whole thing, warn him ahead of time with a message that he better be prepared to wait. Display a Progress bar to help him pass the time.

If you start thinking along these lines, I am sure you will come up with a faster way to present the data to your users...

Just my thoughts...

 

[DirkVFP]

Thanks for the responses,

I'm not actually fetching large amounts of data, but I'm calculating something. This involves summing and joining. Maybe the query can be tweaked abit, but it will still take some time for giving the UI a chance to freeze up.

In anyway, thanks for the tips, I will give the Multithread example a try.

- Dirk

 

[Imaginecorp]

I am sure you are aware you can do plenty while a query is running, even if it not asynchronous. As long as its part of a Select, Scan, Calculate etc. You just cannot interact with the app.
You can display forms with results of your calculations; run programs etc BUT you pay a price in performance depending on the complexity.
If you let us know what you want do do while the query is running, I will show you how to do it

 

[DirkVFP]

(...) If you let us know what you want do do while the query is running, I will show you how to do it

A progress bar showing the query progress is in fact all I want, it's simple and the user knows he has to wait.

 

[Mike Lewis]

Dirk,

For this purpose, a progress bar doesn't count as "user interaction". You should be able to display a progress bar inside your main SQLEXEC() loop (as per your original post).

I do this myself all the time. If it's not working for you, maybe there's something wrong with the progress bar?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[DirkVFP]

For this purpose, a progress bar doesn't count as "user interaction". You should be able to display a progress bar inside your main SQLEXEC() loop (as per your original post).

I do this myself all the time. If it's not working for you, maybe there's something wrong with the progress bar?

Mike,

No, there is nothing wrong with the progress bar, all I wanted to find out with this thread was if I could show a progress bar, and have the user continue to use the form while the query is being executed.

(I forgot to mention in my OP that I'm already using a progress bar informing the user has to wait.)

But all in all, I have the answer I wanted: async executing was not ment for the purpose I had in mind and there are ways of using threads in a FoxPro app, but I'm not going to use them. For now, a progress bar will do fine.

Thanks all for your responses.

- Dirk