RE: Performance of ODBC vs. other access methods

 <snip>
Use existing keys in your queries and you should 
be able to get sub-second response times. 
</snip>

That is an approach I have been trying desparately to put in place here.
Some of the files (read most...) have key fields that make absolutely no
sense (considering that the value is ALWAYS the same...)  the folks here
don't use the field in order by clauses etc.  Using the field since it
is the first key increases performance by exponential factors, I can
only assume that the developers here are afraid of typing an additional
6 characters.....


Thanks,
Tommy Holden


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
richard@xxxxxxxxxxx
Sent: Wednesday, September 06, 2006 8:33 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Performance of ODBC vs. other access methods

Here's a thought.  In fact we've run into this with people building
custom 
indexes over our WebDocs Document Management tables:

Build logical files or SQL views where needed to support inquiries.
Just 
make sure not to specify UNIQUE for any of the keys. 

As long as you don't specifiy UNIQUE on the keys, you can get the 
performance needed without causing problems. 

I don't think too many vendor packages rebuild tables on a regular basis

any more. Hopefully :-)

Then create a CL program that gets run each time you apply a vendor
update 
that deletes your custom views and indexes and rebuilds them after the 
vendor update. 

Think of this as similar to the customization you would do after an
OS/400 
upgrade.  I always advocate storing customizations in a CL program that 
gets run after the OS/400 upgrade. 

Short of that you just have to be very careful with testing when you
write 
queries against your DB.  Use existing keys in your queries and you
should 
be able to get sub-second response times. 

Regards,
Richard Schoen
RJS Software Systems Inc. 
"Providing Your....iNFORMATION NOW!"
Email: richard@xxxxxxxxxxxxxxx
Web Site: http://www.rjssoftware.com
Tel: (952) 898-3038
Fax: (952) 898-1781
Toll Free: (888) RJSSOFT

------------------------------

message: 9
date: Wed, 6 Sep 2006 08:27:05 -0500
from: "Holden Tommy" <Tommy.Holden@xxxxxxxxxxxxxxxxx>
subject: RE: MIDRANGE-L Digest, Vol 5, Issue 1682

 <snip>
index the hell out of your database 
based on your average query types 
</snip>

If only I could...in our shop our main software package is a 3rd party
software & the mandate is to not change or customize the base product
unless it's just a "have-to" case.  Since adding indexes can sometimes
have adverse effects on some base code it's just not always a viable
solution (not arguing at all..In fact I support index usage
whole-heartedly...) unfortunately some of us have to march to the beat
of someone else's drum =(


Thanks,
Tommy Holden