Re: CPU usage for QZDASOINIT

inline

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Walden H. Leverich" <WaldenL@TechSoftInc.com>
To: <midrange-l@midrange.com>
Sent: Thursday, August 29, 2002 1:19 PM
Subject: RE: CPU usage for QZDASOINIT


> OK,
>
> 1) This _should_ be a parameterized query. What is issuing these requests?
> If the query is coming up as a string each time then OS/400 will
reevaluate
> the query plan each time, a _big_ waste of time.

For VB this is my call:
sSQL = "SELECT symbol FROM RUGR80DAT.PMSP0200 WHERE policy0num = '" & _
            polnum & "'"
        Set rdset = cnAS400.Execute(sSQL)

This (and soem other stuff) is inside a Loop.  The polnum value is read in
from a text file.

I had tried using a command object and parameters, but in trying to find out
my previous cursor error, I installed MDAC 2.7.  Now VB bombs out with a
protection fault in msado15.dll when I use a command object for this query.

> 2) Buck has a valid point. Do you care that it's taking too much time?

Yes, because this query is a test of the code and to generate a log of what
we are going to expect from the queries.  Later we are incorporating the
code into a larger program that will run this code again.  We'd prefer the
actual conversion program we are writing nto to take forever.  Also, if I
ever had the diea to write a VB client program for some stuff, the response
time is a bit upsetting (I still need to read the previous links supplied to
the lsit about possible optimization).  I was jsut curious if this time of
response was normal for the type of situation.

> 3) Make sure you have an index (Logical file) that is by Policy0num. If
> possible an index by Policy0num and symbol may even improve performance
> more. Although I'm not sure OS/400 takes advantage of covering indexes.

Okie.

> 4) 7700 done and 12K to go, you're talking about 20K lookups. Don't expect
> it to be sub-second.

I don't expect the whole job to be subsecond, but I do expect each query to
be subsecond.