Re: SQL performance on V5.R4

Just wondering about.

Thank You



From:
"Genyphyr Novak" <Genyphyr.Novak@xxxxxxxxx>
To:
<bpcs-l@xxxxxxxxxxxx>
Date:
01/11/2007 19.44
Subject:
[BPCS-L] SQL performance on V5.R4



message: 3
date: Wed, 31 Oct 2007 19:54:03 +0100
from: Elisabetta.Migliaretti@xxxxxxxxxxxxxxxx
subject: [BPCS-L] SQL performance on V5.R4

Hi All



Scenario: BPCS 6.1.00 - OS400 V5.R4



Any SQL performance problem in BPCS specifying *YES for the parameter
"IGNORE_DERIVED_INDEX" in QAQQINI file ?



Thanks
------------------------------------------------------


Yes, we have seen that there is a big degradation in performance of BPCS
if you do this. The BPCS database has many many select/omit logical
files in it that are used to run SQL statements and many of them are
'sparse indexes' meaning the size of the select/omit logical is smaller
and easier to put into memory than a similarly keyed SQL index w/o the
select/omit logic.

If you change this parameter to ignore them, many of those statements
must resort to table scans (or if you take the time to identify and
create similar SQL indexes, it will use larger SQL indexes and often the
queries are still slower than with CQE and the original sparse logical
files). Out of the box, BPCS will use CQE, not SQE to run 99.99% of
queries because the database is full of select/omit logicals, and that
works just fine and just as fast on V5R4 as it did on prior releases.

Are you having some performance issue where someone is telling you to
run queries in SQE as the 'solution' or is this just something you were
wondering about?

Thanks,


Genyphyr Novak | Senior Architect, ERP LX Architecture | Infor |
genyphyr.novak@xxxxxxxxx