RE: Performance of ODBC vs. other access methods

Centerfield Technology offers a database assessment that, amongst other things, 
makes suggestions for indexes and estimates whether it is wise to do so, based 
on the cost of maintaining the indexes due to insert, updates, and deletes (not 
just via SQL, BTW). And this maintenance is not a human administration (refer 
to the folly recently bandied about in this regard) - it does take cycles for 
the system to do what it does. www.centerfieldtechnology.com is their address, 
look up database assessment or some such.

-------------- Original message -------------- 
From: "Wilt, Charles" <CWilt@xxxxxxxxxxxx> 

Rob, 

I don't disagree with the idea that one shouldn't be afraid of adding 
indexes due to performance implications, thus my use of the phrase "add 
lots of indexes". 

How many is "lots"? It depends of course. 

No matter how efficient IBM has made the implementation, maintenance on 
access paths does have a cost. If you get stupid with the indexes, for 
example a large file with 3,686 indexes (the max) built over it, you're 
probably going to have a problem! Then again, you could be running a 64 
processor 595 with max RAM installed. 

Would I add an index useful to a query run once a year? Probably not. 

Would I add an index useful to a query run once a day? You betcha. 



Charles Wilt 
-- 
iSeries Systems Administrator / Developer 
Mitsubishi Electric Automotive America 
ph: 513-573-4343 
fax: 513-398-1121 

-----Original Message----- 
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx 
Sent: Wednesday, September 06, 2006 10:11 AM 
To: Midrange Systems Technical Discussion 
Subject: Re: Performance of ODBC vs. other access methods 

Charles, 

I have a 1998 copy of the AS/400 Experts Journal Volume I, 
Number I on my 
desk. It has an article in there by Skip Marchesani in which 
he states 
that it is a MYTH to believe that "logical 
files on a file are resource intensive, have a negative impact on 
performance, and should only be used when absolutely 
necessary". He goes 
on to state that IBM cleaned this up on V2 of OS/400 and even 
better in V3 
of OS/400. Others say they have testing that disputes Skip's 
statements. 

Granted, in the early days, John Sears used to tell story after story 
about how people went wild with Logical files and seriously impacted 
performance. But John has been retired for a few years now 
and the OS has 
marched on. 

Rob Berendt 
-- 
Group Dekko Services, LLC 
Dept 01.073 
PO Box 2000 
Dock 108 
6928N 400E 
Kendallville, IN 46755 
http://www.dekko.com 





"Wilt, Charles" 
Sent by: midrange-l-bounces@xxxxxxxxxxxx 
09/06/2006 09:49 AM 
Please respond to 
Midrange Systems Technical Discussion 


To 
"Midrange Systems Technical Discussion" 
cc 

Subject 
RE: MIDRANGE-L Digest, Vol 5, Issue 1682 






"Since adding indexes can sometimes have adverse effects on some base 
code" 

Huh? 

Since when, what adverse effect? 

If you add lots of indexes on a volatile file, you could see a 
performance hit due to index maintenance. But other than 
that, I'm not 
aware of any "adverse effects" indexes might have on base code. 

Charles Wilt 
-- 
iSeries Systems Administrator / Developer 
Mitsubishi Electric Automotive America 
ph: 513-573-4343 
fax: 513-398-1121 

-----Original Message----- 
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Holden Tommy 
Sent: Wednesday, September 06, 2006 9:27 AM 
To: Midrange Systems Technical Discussion 
Subject: RE: MIDRANGE-L Digest, Vol 5, Issue 1682 


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 

-- 
This is the Midrange Systems Technical Discussion 
(MIDRANGE-L) mailing 
list 
To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
or email: MIDRANGE-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/midrange-l. 


-- 
This is the Midrange Systems Technical Discussion 
(MIDRANGE-L) mailing list 
To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
or email: MIDRANGE-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/midrange-l. 

-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
or email: MIDRANGE-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/midrange-l.