Re: Debugging ODBC application

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