See the suggestions below ...

Thanx much,

Rajib Sarkar
Advisory Software Engineer (RAS)
IBM Data Management Group
Ph : (602)-217-2100
Fax: (602)-217-2100
T/L : 667-2100

As long as you derive inner help and comfort from anything, keep it --
Mahatma Gandhi



"Rajasekaran,
Rajesh" To: "'informix-list (AT) iiug (DOT) org'" <informix-list (AT) iiug (DOT) org>
<RRajasekaran@fores cc: "'sapmix (AT) iiug (DOT) org'" <sapmix (AT) iiug (DOT) org>
tpharm.com> Subject: Clarification on SQL performance tuning....
Sent by:
owner-informix-list
@iiug.org


09/15/2003 09:55 AM







Hello all,

* I would like to discuss a SQL performance issue here and i am hoping to
get some suggestions/tips here. We have SAP R/3 46c running on* IDS 7.31
UD2XG on Solaris 9 . Here is the issue.

*The query should extract some records based on the date filter from "mkpf"
table and joins those records to "mseg" table where the material records to
be picked.Also, it has to join with "mcha" to pick some more columns for
the resultant set. There were some couple of other tables(small in size)
which i didnt include here was a part of the original SQL to pick some more
information. When i break it up the SQL by introducing tables and join
conditions for those tables one by one, i found out that the delay happened
only when i introduce the "MCHA" table and its related joins. Hence my SQL
& its query plans pasted here involved only those tables and joins.

The whole query takes 30-40 mins to get the results. Please find the
attached table info for 3 specific tables and the SQL query optimizer plan.

Questions
--------------

1) Does the query path chosen here get executed in the same sequence as it
shows in the SQL plan ?
*** -- I mean does the optimiser sequence in terms of* applying join and
filters as it shows on the query plan like first on "marm",then on "mcha",
then on "mseg", then on "makt",then on "afpo",then on "mkpf".
-- YES

2) Ideally i feel based on the table data & considering the type of
application data stored, size etc. , the query can be better off by
choosing the route* "mkpf", "mseg","mcha" order to get the desired records.
This can happen if the optimizer chooses hash join i guess since the MSEG
and MKPF are bigger tables of size. I have seen before sometimes the
"estimation cost shows high figure" and the query results come in quite a
good time but not for this case though.
-- If that's the case, you can re-order the order of the tables in the FROM
clause and force the optimizer to use the order using the ORDERED hint
Check the cost, after you run it with the ORDERED hint ... u'll
definitely see the cost to be higher than what the optimizer is showing
without the ORDERED hint. But, then the execution time of the query and the
COST are not always directly related .... (COST is a relative cost and is
an approximation)

3) I have the update statistics executed upto date for these tables. at 0%
from sapdba tool with default suggested method. Do* you think the optimiser
behaving wrongly here ?

*** Our OPTCOMPIND is supposed to be 0 for our SAP R/3 environment. Hence
the optimizer prefers nested loop join by default.
-- U never know... there are OPTIMIZER bugs .. it would require more
research to conclude that its a bug.

4)* Do you think this SQL can be re-framed in any order to get better
results ?
-- Possibly yes... Here is how I will proceed if I were you ...
-- Do a select count(*) with the join conditions for T_04 and T_05 to see
what's the number of rows selected, and proceed to find out which would
eliminate the most # of rows from the resultant set (by trying different
joins).
-- Essentially the goal is to make the resultant set to be small so that
the next join works faster instead of joining un-necessary rows and then
discarding them later on. The optimizer will try and decide that for you
(there was a paper on 'Peephole optimization' some time back) but it can do
so much.

5)* Also on MKPF table, there is another index with "mandt,budat,mblnr".
Ideally the date search should have used this index. But i think bcos of
the join condition involved between MKPF & MSEG on the sql, the optimiser
always choose the unique index (mandt,mblnr,mjahr) and apply the date
filters on that index. Is there any way to change that behaviour ? Iam
right now testing the SQL with optimiser hints like forcing a specific
index,hash join etc.
-- U will have to try Optimizer hints for that ...

Any suggestions/comments* are greatly appreciated.

Thanks
Rajesh Rajasekaran
Informix Database Administrator
Forest Pharmaceuticals Inc.
(314) 493-7073
rrajasekaran (AT) forestpharm (DOT) com




#### sqexplain.out has been removed from this note on September 15, 2003 by
Rajib Sarkar


sending to informix-list

Rajasekaran, Rajesh wrote:

You will get more help if you post in text only.

Yes.

Yes.

I would think it is more likely to join on the smaller tables first with
index joins. Inaccurate estimates can be indicative of inaccurate or
missing statistics. If for example you have a very large table with a
skewed data distribution, then LOW or MEDIUM statistics may not help much.

I can't tell. I don't know what statistics you updated. I don't know what
the relationships are between the tables. I don't know all the data volumes
involved, etc.

I think you may be missing joins. For example:

mkpf.mandt = mseg.mandt
mkpf.mandt = afpo.mandt
mkpf.mandt = makt.mandt
mkpf.mandt = marm.mandt
mkpf.mandt = mcha.mandt

If those are the correct relationships, and get rid of:

mseg.mandt = '040'
afpo.mandt = '040'
makt.mandt = '040'
marm.mandt = '040'
mcha.mandt = '040'

But without seeing the relationships between the tables, in terms of the
primary and foreign keys, it is difficult to tell.

The optimiser will use indexes that appear to eliminate the most records.

That's the way you change the optimiser's mind, but be very careful that
you don't make things worse. :-)

I have seen query performance improved with optimiser hints, only to get
worse again after the next UPDATE STATISTICS is run.

Check that your UPDATE STATISTICS commands are up to date and specific
enough for your data distribution.

Check the joins between each table pair. Even extract these joins and run
them separately to see that you are getting the desired records.

Check the indexes on the join columns.

Check the physical location of these tables / fragments. I'd hate to think
you are joining tables on the same disk. ;-)

Check that your expectations are realistic. Perhaps 30 mins is a good time,
given a six-way join and the data you have.

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+


sending to informix-list