Re: Debugging ODBC application

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Oliver

Single-record retrieval is typical of a VB app that uses the JET engine
(DAO) to run ODBC instead of RDO (ask your VB folks). It could also have
other causes, but that's common. JET does the least common denominator thing.

You could start with this site
<http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/rzaik/rzaikappodbc.htm>
on Express ODBC, that has performance links and programming examples links.
It's a condensed version, AFAIK, of older ODBC manuals that had excellent
coverage of this stuff. Try
<http://publib.boulder.ibm.com/pubs/pdfs/as400/V4R2PDF/qbkade01.pdf> - it
says it's for Win95, but it's still applicable.

At 08:06 PM 11/6/2002 +0100, you wrote:
> Hello,
>
> well, I'm still debugging this ODBC application. It is written/maintained
> in VisualBasic by
> another company and running on a Windows2000 server. It uses the Client
> Access Express
> ODBC driver.
>
> We have heavy performance problems with this VB-app. With STRDBG and some deep
> digging I found that the app is doing a
>
>           select * from vrosif where sistat='40' or sistat='41' or
> sistat='43'
>
> which returns about 1000 to 5000 records. In the QZDASOINIT joblog I see
> several
> thousand log entries like:
>
>               1 record retrieved from vrosif.
>
> Obviously, the VB-app does a fetch for every single record of the query.
> This easily takes
> 30 seconds.
>
> Doing the query manually through the tool SQLTHING I see  a few messages like
>
>              500 records retrieved from vrosif
>              500 records retrieved from vrosif
>              297 records retrieved from vrosif
>
> This easily executes in 1-2 seconds!
>
> I need to tell the VB-programmer how to achieve this (I have no knowledge
> about VB and
> ODBC programming).
>
> Are there settings in the ODBC-driver for this record blocking or has this
> to be programmed
> in VB? Some sample lines of code would be nice..
>
> Thanks in advance,
>
> Oliver Wenzel