Re: ODBC Perfomance issue

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Oliver, ODBC may not have been the culprit. What kind of data access was 
the VB application using, DAO, RDO, or direct calls to the ODBC API? Or 
other? DAO will be dreadfully slow, since it uses the JET engine native to 
VB and MS Access. That process can have communication requests 2 or 3 
orders of magnitude greater than direct API calls. RDO is a thin layer over 
the APIs. OLE/DB (ADO) is also a very thin layer, and the CAE driver, at 
least, ends up using the same mechanism at the host.

So, if the means of using ODBC was inefficient, you will see great gains, 
just by going to OLE/DB, just because it does things better, less 
intervening processing.

Check out the ODBC User's Guide for Win 95/NT at the 
<www.iseries.ibm.com/clientaccess> site under the Library link - excellent 
info and comparative data on different access methods.

Vern

At 08:32 AM 1/30/2003 +0100, you wrote:

> Hello,
>
> we currently have several big projects that use ODBC - and we had lots and
> lots of performance problems.
> Not so much on the AS/400, but on the application running on the pc
> server.. Very simple SQL-statements and
> only 2 or 3 files on the AS/400 that were used (and all less than 20.000
> records).
>
> We are now switching to OLE-DB, which, on the first try, seems to be 5
> times faster than ODBC.  Updates that
> took about 200ms through ODBC where done in about 40ms with OLE-DB. I know
> that isn't accurate, just some
> stopwatch checking, but factor 5 (for minimal program changes) seems to be
> promising.
>
> We will be running some more in-depth test in the near future..
>
> Regards,
>
> Oliver
>
>
>                       "g man" 
> <g.man2000@lycos.com> 
>                       Sent 
> by:                                                          To: 
> "Midrange Systems Technical Discussion"
>
> midrange-l-bounces+oliver.wenzel=cibavision.novartis.com@m 
> <midrange-l@midrange.com>
>                       idrange.com 
>                cc:
>
> Subject:  ODBC Perfomance issue
>
>
>                       28.01.03 
> 19:07 
>                       Please respond to Midrange Systems Technical 
> Discussion 
>
>   I am working with a 'legacy' type application that interfaces a VB
> application (using ODBC) with an AS/400 system file with a trigger on it to
> process the data to update yet another set of files.
>
> The problem being experienced is a huge performance hit on the AS/400 - I
> had heard that ODBC was slow but bringing the system to it's knees is
> another issue.
>
> Is there any tuning I could investigate to make this more efficient..or is
> there a way to direct the VB job as it signs on to the 400 to a separate
> subsystem or some such..
>
> I'd appreciate the help..
>
> Girish