× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.