Cheers chaps will investigate further.
Having to investigate dayend function, as doubling processors from two to
four, made no difference.
So I/O bound?
Main storage not an issue I believe, as subsystems in calc mode.
This appears to mean only way of improving is either new logical files or
rewriting programs.
Many of reporting functions use query or sql, so much fun to be had.
Any tips and/or hints from those who have had similar opportunities
gratefully accepted.
Dealing with a package written by those not overly interested in efficiency!
:-(
Kind Regards
Ian
----------------------------------------------------------------------
message: 1
date: Mon, 21 Apr 2008 13:22:15 -0500
from: "Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
subject: RE: Index Advisor and MTI's.
MTIs are created when query optimizer thinks benefit of creating an index
outweighs the cost of building and maintaining one. This usually means
'small' tables that are frequently queried with similar/identical statements
(there's more to it but let's not get bogged with details we can't affect in
any way). They are not built over large files or files where update activity
dominates read activity.
You get the benefit of creating an index immediately, you do not have to
wait for an IPL.
System Index Advisor advises not only MTIs but full indexes you should build
over large files as well, including SQE and CQE advice. So, it really is a
full set of index advice. Now which of the many recommended indexes will
help your critical processing... you, Mr. DBA, have to make that connection.
As for programmers taking efficiency into account when designing solutions -
of course they should. But I've seen many times where they can't due to
separation of duties (i.e. they're not allowed on production box), or they
just don't know enough about database design (i.e. an SQL join must have an
index over join columns; if it doesn't you get a Cartesian Product join and
can complete a vacation before your query finishes). Or they put in an
ORDER BY on the query where they really don't need one. Or they just don't
know there is a problem with the statement.
This is where part time DBA comes in and AT THE LEAST is able to point out
to the programmer "this statement is performing poorly - fix it".
HTH, Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: Index Advisor and MTI's.
Hi All,
Possibly a stupid question or have miss-read overview of index
advisor?
If MTI's are created, after several indications that an index is
required?
Do you only get the benefit of creating a permanent index after an
IPL?
Trying deduce how much benefit there is taking the advice from index
advisor.
Any constructive input gratefully received.
Believe that programmers should take into account efficiency when
creating solutions.
Kind Regards
Ian
message: 7
date: Mon, 21 Apr 2008 14:56:58 -0400
from: rob@xxxxxxxxx
subject: Re: Index Advisor and MTI's.
Had to lookup what an "MTI" was. Found a reference to Maintained
Temporary Indexes here:
http://archive.midrange.com/midrange-l/200703/msg01182.html
I am not sure what spawns their (MTI) creation. So I can't answer your
second question.
A permanent index should be used right away and not have to wait for IPL.
Here's your proof:
I created a procedure by running RUNSQLSTM against this source member:
***** Start of source
CREATE PROCEDURE ROB.TEST
LANGUAGE SQL
BEGIN
DECLARE COUNTER INT DEFAULT 0;
CREATE TABLE ROB.BISMUTH (MYKEY INT , THENBR DEC (15 , 5));
WHILE COUNTER < 10000 DO
INSERT INTO ROB.BISMUTH (THENBR) VALUES(rand(1)*100);
SET COUNTER = COUNTER + 1;
END WHILE;
create table rob.bismuth1 as
(select * from rob.bismuth
order by mykey)
with data;
create table rob.bismuth2 as
(select * from rob.bismuth
order by thenbr)
with data;
CREATE INDEX ROB.bismutha ON ROB.BISMUTH (MYKEY ASC);
CREATE INDEX ROB.bismuthb ON ROB.BISMUTH (thenbr ASC);
create table rob.bismuth3 as
(select * from rob.bismuth
order by mykey)
with data;
create table rob.bismuth4 as
(select * from rob.bismuth
order by thenbr)
with data;
END
***** End of source
Then I went into Run SQL Scripts and ran it by CALL ROB.TEST;
When it created table 1 and 2 it advised the indexes later created. When
it created 3 and 4 it did not call for any indexes.
Granted, it didn't use them, all four tables used full table scans
(according to VE).
I modified the source to the following:
***** Start of modification
create table rob.bismuth1 as
(select mykey, thenbr from rob.bismuth
order by mykey)
with data;
create table rob.bismuth2 as
(select mykey, thenbr from rob.bismuth
order by thenbr)
with data;
CREATE INDEX ROB.bismutha ON ROB.BISMUTH (MYKEY ASC, THENBR ASC);
CREATE INDEX ROB.bismuthb ON ROB.BISMUTH (thenbr ASC, MYKEY ASC);
create table rob.bismuth3 as
(select mykey, thenbr from rob.bismuth
order by mykey)
with data;
create table rob.bismuth4 as
(select mykey, thenbr from rob.bismuth
order by thenbr)
with data;
***** End of modification
Cut a few milliseconds off. Table 3 and 4 were built with only an index
scan, and no table scan. Why? Because all columns needed in the select
were already in the index. Thus bypass the table completely.
Rob Berendt
midrange.com
