Re: Performence guideline for DB design

Tejasvi,

In a message dated 97-04-18 11:51:14 EDT, you write:

>      We are developing an application in which client is VB and server is 
>       AS/400. We are trying to establish some database design guidelines in

>       order to achieve sub second response for all screens in an
application.
>       
>       For this, we think the following factors affect response :
>       
>       i)         Size of physical file

Definitely.  The "break-over" point for performance degradation seems to be
about 200K records.

>       ii)        Number of logical files

Shouldn't matter unless you are doing massive record updates.

>       iii)       Record length of physical file

Depends upon your transport mechanism.  You're NOT planning on using ODBC are
you?  ODBC is greatly affected by record length.  An APPC alternative isn't
affected by it as much.

>       iv)        Key length of logical file

Shouldn't be a problem.

>       v)         Time required to read a record 
>       vi)        Time required to update a record 
>       vii)       Time required to write a record
>                  (points v, vi and vii should take into consideration the
   
>                  number of access paths, since more the number of logicals,
 
>                  higher is the I/O time)

I'm confused about v-vii.  These shouldn't be any different that your native
applications.

>       viii)      Other factors such as machine overheads (number of jobs
    
>                  running, pool in which running, if AS/400 being accesses
   
>                  over a network etc.)

This can be a problem.  I'd highly suggest putting your COMM programs in
their own pool (other than QCMN).

>       Based on a shortlist of things affecting I/Os, we would like to run a

>       benchmarck which can measure I/O time - for example, one benchmark 
>       with large number of records, large number of logicals, huge record 
>       length, another benchmark with large number of records, small number 
>       of logicals and small record length and so on.
>       
>       Based on a matrix which we get by running the benchmark, we would 
>       evaluate each screen in the application against this matrix and 
>       figure out the overall response time we can expect. If the desired 
>       response is not expected, we might go ahead and either change 
>       database (if possible) or change screens so that response time is 
>       lower.
>       
>       Questions are :
>       
>       i)         Has anyone worked on a similar thing before ?

Yes.  Two weeks ago we benchmarked an application using VB via ESS/400 to
DB2/400 against the same thing using MS/Access via ODBC to an SQL/Server
database.  We used about 60K records, and the /400 application out-performed
its PC counterpart by a factor of eight!  Of course, comparing Access
applications to VB applications is like comparing apples to oranges, but
worst-case I'd expect a factor of 4 performance improvement.  And we're not
even running a server system!

>       ii)        Any book which can help us out with this ?

We haven't found much useful in the IBM manuals, and AS/400-related C/S
publications are few and far between.  Trial and error has been our greatest
ally.

>       iii)       Any published statistics / documents on this which can be
  
>                  used ?

Don't know.

>       iv)        Any other suggestions ?

Use an APPC connect product like ESS/400 (www.ess.com) over ODBC if at all
possible.  I haven't tried "lightning" personally, but I understand that it
still suffers from the same limitations as other ODBC products -- it's just
faster than IBM's ODBC used to be.

HTH,

Dean Asmussen
Enterprise Systems Consulting, Inc.
Fuquay-Varina, NC  USA
E-Mail:  DAsmussen@AOL.COM

"There is no abstract art.  You must always start with something." -- Pablo
Picasso
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This is the Midrange System Mailing List!  To submit a new message,   *
* send your mail to "MIDRANGE-L@midrange.com".  To unsubscribe from     *
* this list send email to MAJORDOMO@midrange.com and specify            *
* 'unsubscribe MIDRANGE-L' in the body of your message.  Questions      *
* should be directed to the list owner / operator: david@midrange.com   *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *