ODBC SET OPTION(ODBC Asynchronous)

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

ODBC SET OPTION(ODBC Asynchronous)

John Flynn-2
Hi. I am using the built-in ODBC calls in 4Dv2004.4. I was hoping someone
knows more about this than I and can advise me. I'm having trouble with
the ODBC "asynchronous" setting. First of all, does anyone know what that
setting does? I've researched and found that there is a sync/async option
in the ODBC spec, but Microsoft seems to advise that the async option is
only for ancient single-threaded operating systems, and modern Windows
apps should all be using the sync option and they should rely on the
inherent multithreading in the applications to handle concurrent ODBC
operations. When I try the 4D sync option, the effect in 4D is that while
a long-running ODBC operation is in progress, the entire 4D environment
freezes up tight (just like the old 4D SQL Server used to do). Seems to me
this is a pretty useless option, as it appears to circumvent Microsoft's
advice to let the PC multithread the concurrent operations. (Is this all
because 4D uses cooperative multitasking rather than OS threads?) On the
other hand, when I switch to async mode, then 4D allows concurrent
processes to continue running while an ODBC operation is pending. That's
great, except that I've done extensive timings and found that each ODBC
operation now takes roughly an extra quarter-second to complete. Fine if
you're doing just one operation or a long-running one, but if it's in an
automated loop of some type (like an interface engine) that throttles your
throughput down from perhaps hundreds of operations per second, to a max
of 4 operations per second. My theory is that when in async mode, 4D
internally uses the true ODBC async calls but polls at huge quarter-second
intervals for a reply (even when some replies really come back in just a
few milliseconds). If it matters, I'm using the TDS flavor of Sybase's
ODBC driver for Windows, version 4.20.0067. Anybody have any advice for
me?

**********************************************************************
Get under the hood... with 4D 2004's "How Do I?" examples.
http://www.4d.com/products/examples_underhood.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  <http://www.4d.com/support/faqnug.html>
Archive:  <http://dir.gmane.org/gmane.comp.lang.inug-4d.tech>
Unsub:  mailto:  [hidden email]
**********************************************************************

Reply | Threaded
Open this post in threaded view
|

Re: ODBC SET OPTION(ODBC Asynchronous)

Scott Gelvin
John,

You are correct that 4D isn't multi-threaded. So I'd look at the  
Async mode not as a "throttle down", but as a "give something else a  
chance to do something".

Option 1: Use the Async option, accept the performance drop, and add  
an animated progress or activity message as a separate process for  
the duration of the ODBC operation, to soothe your users and help  
them feel like it's not taking so long. (You may actually achieve the  
perception of "it's faster"!)

Option 2: Go with the 4D ODBC Pro plug-in and exercise all the  
control you'd like over the operations.

Option 3: Use the Sync option, train your users to NOT TOUCH ANYTHING  
until it's done. (I don't know of an end user that likes this. Even  
when I choose this, it's not too long before I bemoan my own decision.)

Scott Gelvin
Footprints, Inc.

On Jul 11, 2006, at 5:31 PM, John Flynn wrote:

> Hi. I am using the built-in ODBC calls in 4Dv2004.4. I was hoping  
> someone knows more about this than I and can advise me. I'm having  
> trouble with the ODBC "asynchronous" setting. First of all, does  
> anyone know what that setting does? I've researched and found that  
> there is a sync/async option in the ODBC spec, but Microsoft seems  
> to advise that the async option is only for ancient single-threaded  
> operating systems, and modern Windows apps should all be using the  
> sync option and they should rely on the inherent multithreading in  
> the applications to handle concurrent ODBC operations. When I try  
> the 4D sync option, the effect in 4D is that while a long-running  
> ODBC operation is in progress, the entire 4D environment freezes up  
> tight (just like the old 4D SQL Server used to do). Seems to me  
> this is a pretty useless option, as it appears to circumvent  
> Microsoft's advice to let the PC multithread the concurrent  
> operations. (Is this all because 4D uses cooperative multitasking  
> rather than OS threads?) On the other hand, when I switch to async  
> mode, then 4D allows concurrent processes to continue running while  
> an ODBC operation is pending. That's great, except that I've done  
> extensive timings and found that each ODBC operation now takes  
> roughly an extra quarter-second to complete. Fine if you're doing  
> just one operation or a long-running one, but if it's in an  
> automated loop of some type (like an interface engine) that  
> throttles your throughput down from perhaps hundreds of operations  
> per second, to a max of 4 operations per second. My theory is that  
> when in async mode, 4D internally uses the true ODBC async calls  
> but polls at huge quarter-second intervals for a reply (even when  
> some replies really come back in just a few milliseconds). If it  
> matters, I'm using the TDS flavor of Sybase's ODBC driver for  
> Windows, version 4.20.0067. Anybody have any advice for me?
**********************************************************************
Get under the hood... with 4D 2004's "How Do I?" examples.
http://www.4d.com/products/examples_underhood.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  <http://www.4d.com/support/faqnug.html>
Archive:  <http://dir.gmane.org/gmane.comp.lang.inug-4d.tech>
Unsub:  mailto:  [hidden email]
**********************************************************************

Reply | Threaded
Open this post in threaded view
|

Re: ODBC SET OPTION(ODBC Asynchronous)

John Flynn-2
In reply to this post by John Flynn-2
Hi Scott,

> You are correct that 4D isn't multi-threaded. So I'd look at the
> Async mode not as a "throttle down", but as a "give something else a
> chance to do something".
>
> Option 1: Use the Async option, accept the performance drop, and add
> an animated progress or activity message as a separate process for
> the duration of the ODBC operation, to soothe your users and help
> them feel like it's not taking so long. (You may actually achieve the
> perception of "it's faster"!)

Yes. However there is a limit to that. If the particular operation
(composed of many SQL queries) used to take 5 seconds and now it takes 30
seconds, a progress bar won't fool too many of my users. :-)  Anyway, the
application I'm most concerned about is an unmanned interface engine. It
just has to work fast. (I.e. its rate of output MUST be equal or greater
than its rate of input.)

> Option 2: Go with the 4D ODBC Pro plug-in and exercise all the
> control you'd like over the operations.

Mine is a vertical market application, and ODBC Pro is too expensive for
me, even after negotiating an OEM price. I'd rather accept the current
limitations in the built-in commands; though I reserve the right to
grumble about them.

> Option 3: Use the Sync option, train your users to NOT TOUCH ANYTHING
> until it's done. (I don't know of an end user that likes this. Even
> when I choose this, it's not too long before I bemoan my own
> decision.)

Yes, that's what I'm used to with the old 4D SQL Server, and it'd be such
a shame to be forced into that mode when the async capability is so
tantalizingly close.

In my interface engine I'm trying to use an Option 4, which is to have
multiple concurrent worker processes that load-balance the work. Each one
does its own ODBC connection, in async mode, and each suffers the articial
time delays, but it appears that as a group they get multitasked by 4D
efficiently. So while one worker process is spinning in an artificial ODBC
idle loop, another one can be doing real work. But I wish async worked
better in the first place.

Thanks.  - John.

**********************************************************************
Get under the hood... with 4D 2004's "How Do I?" examples.
http://www.4d.com/products/examples_underhood.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  <http://www.4d.com/support/faqnug.html>
Archive:  <http://dir.gmane.org/gmane.comp.lang.inug-4d.tech>
Unsub:  mailto:  [hidden email]
**********************************************************************