Re: Adding SQL indes causing performance degradation? -- MIDRANGE-L

it's a well known fact that adding indexes has a performance impact for
update jobs. Seizes over the index plus checking to see if the index needs
updating and then performing the update to the index are added to the job
the performs the update.

there are several ways to deal with this. None of which is better than
another, it's just something you have to figure out for your situation:

1. Leave it alone, eat the time on the update. The reason to do this is if
you get significant performance benefit from having the extra indexes and
can live with the increase in update time.

2. Change the maintenance of the indexes created. The reason to do this is
you need the indexes, can live with the rebuild or update time taken during
open and use of the index, but can't live with the increased update time.

3. Remove the index members. You may not be able to do this with SQL created
indexes, but it was a technique in using DDS indexes. Remove the member. Run
the update. Create the member. This rebuilt the index each time the member
was added back. Again, you have to live with the create time.

4. Look for other indexes that are not being used or may be redundant and
remove them. You can do this with the database monitor and Visual Explain.

Index tuning is not a simple matter. Especially with 89 million records.
Indexes carry overhead. Every index really needs to overcome the overhead in
order to be useful. If you add an index that gets used once a year and saves
you 20 minutes of processing time, but costs you 1 millisecond 5000 times a
day to update... it's probably not a "usefull" index.

Only you, the users and the people that set the expectation level on the
machine can determine what's best.

And even at that, it isn't always easy.


===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified Specialist - iSeries Administrator
 -- IBM Certified Specialist - RPG IV Developer

"When I die, I want to die like my grandmother who died peacefully
  in her sleep. Not screaming like all the passengers in her car."

    - Author Unknown

----- Original Message -----
From: "Chevalier, Rick" <Rick.Chevalier@xxxxxxxxxxxxxxx>
To: "Midrange Mailing List (E-mail)" <midrange-l@xxxxxxxxxxxx>
Sent: Tuesday, April 08, 2003 10:56 AM
Subject: Adding SQL indes causing performance degradation?


> Does anyone have experience with existing applications slowing down after
> adding 1 or more SQL indexes to a physical file used by the application?
>
> Here are the details.
>
> Our nightly payment processing writes each received payment to a
historical
> transaction physical file.  This file contains 89+ million records.  In
> January we created three SQL indexes over this file to improve query
> processes.  Evaluating the run times compared to transaction volume shows
a
> significant spike in run time without an increase in transaction volume
> around the time these indexes were created.  The job is consistently
running
> about 20 minutes longer than before the indexes were created.
>
> Friday we removed all but one index that is part of other production
> applications.  The run time did not improve.
>
> The SQL index is being pointed to as the reason for the longer run time.
I
> can't come up with a reason this would be either true or false.  It seems
to
> me it wouldn't impact performance any more than adding another DDS created
> logical.
>
> The statement used to create the index is:
>  create index lnp00701s4 on lnp00701 (lhtc1)
>
> Anyone have any ideas?
>
> Rick
>
> _______________________________________________
> 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.
>