I have been experimenting with the various properties and issues associated with synchronous and asynchronous processing of SPT statements (and of remote views).

I like the idea (in theory) of using asynchronous processing, coupled with progressive fetching, so that I my program can immediately return from the sqlexec statement, and start to 'examine' the data (the limitations of what can be done prior to the full completion of the query is another issue I am grappling with - but more on that later).

However, in my testing so far I have found that asynchronous processing is dramatically slower than synchronous processing, to the degree that, in my view, I am better off abandoning asynchronous processing altogether and that instead I should develop techniques to break up my synchronous queries into parts, and do unions on the parts.

I am hoping that there is some property setting or technique that I am overlooking or unaware of that will allow me to optimize asynchronous processing.

In my testing, in retrieving 85K rows from a sqlserver2K table, testing on my local machine, a pentium 4, 1.5 gh, 512 megs ddr ram, 7200rpm ide drive, win2kserver, vfp6, it takes about 8 seconds to retrieve 85K records into a cursor using synchronous processing. When calling sqlexec in a loop with asynchronous processing, it takes about 180 seconds. I have tried all sorts of combinations of fetchsize, fetchasneeded, etc. they don't seem to make a BIT of difference, the query always finishes in 180 seconds.

BTW - in a previous thread, I talked about the difference between synchronous SPT and using a remote view, for the same table, and same test environment - and compared the amount of time it takes to fully download the remote view - again, regardless of fetchsize, etc. The remote view also takes about 180 seconds to 'fully download'. It seems as if the remote view is always operating in the same manner as an asynchronous spt cursor, regardless of the property settings that I apply to it.

In my next post I would like to discuss the techniques that I am considering using if I decide that I have to abandon asynchronous processing. But I am hoping that I am missing something, and that there is a way to speed up asynchronous processing, and that someone will clue me in on what it is.

I understand, in general, that with asynchronous processing vfp is now free to do more work. But since it is sql server that is doing the query work, I don't understand why it should take any longer to execute, unless it is the case that once control is returned to vfp, in the foreground, it is getting way more timeslices than sql server - I haven't tested this on a lan, only on my local machine - I am not that well versed on OS issues, windows event model, etc. so - these are just guesses.

Any suggestions would be appreciated.

TIA

****slow asynchronous processing
**** regardless of fetchsize, fetchasneeded
lnhandle=sqlconnect('vamconnect')
sqlsetprop(lnhandle,'asynchronous', .t.)
sqlsetprop(lnhandle,'batchmode', .t.)
*cursorsetprop('fetchsize',20000,0)
*cursorsetprop('fetchasneeded',.f., 0)
lnstart=seconds()
do while .t.
	lnresult=sqlexec(lnhandle,'select * from sostrs','mycursor')
	if lnresult>=1
		exit
	endif

enddo
?seconds()-lnstart  && about 180 seconds for 85K rows

*****************************
***fast synchronous processing
lnhandle=sqlconnect('vamconnect')
sqlsetprop(lnhandle,'asynchronous', .f.)
sqlsetprop(lnhandle,'batchmode', .f.)
lnstart=seconds()
lnresult=sqlexec(lnhandle,'select * from sostrs','mycursor')
?seconds()-lnstart  && about  8 seconds for 85K rows