Re: optimizing - sqexplain

Nils Myklebust wrote: > > ronald.velghe@trasys.be ("Velghe, Ronald") wrote: > > : > :Hi, > : > :We are developing an application with New Era 2.20 using Online 7.10 > :(about to upgrade to 7.23). > :My goal is to optimize the response times and I'm new in that function. > : > :The first idea is to search through the access plan for high cost > :queries > > The first idea should allways be to know sql well enough to write high > performance statements. You will have to read a lot about sql of > course, but there is no real way arround experience. In the beginning > you test all kinds of combinations to see what happens. > > The second idea should be to know when you have a statement that might > be slow. This should then be tested against a realistic size database > for performance. You can then either use set explain on for this > statement alone or test it in dbaccess. > Most sql statements in your application should be no problem. > > :, the problem is that I don't know any way to make a link between > :the queries in the sqexplain.out and the part of the code where the > :queries are used. > > This we do by selective use of set explain so not every statement is > in the sqexplain.out file. > > :Another problem is that all the statements are dynamic and the access > :plan is calculated at runtime, is there any way to use static sql > :statement, or a way to force pre-calculation of the access plan like for > :stored procedure. > > The above test methods will solve this. "Static" sql statements, if > there where such a thing in Informix, would hardly solve any problems > as to performance. If you could force a pre-calculation of the access > plan it would (or should) have to be recalculated when there are > changes to the database. The same is true for stored procedures. These > changes would include not only table changes, but indexes, relative > size of tables as well as data distribution. If you do your sql > programming and database administration well the dynamic execution of > sql should work very well across such changes. > > :I'd like to know if a cookbook for optimizing exists, or if there are > :some well-known rules to follow > > There are no cookboks for design or development of high performance > applications. Knowledge and experience is the only name of the game. > There are of course a lot of well known rules discussed in various > literature. There are lots of good books about it. I have learned most > from all the books of C.J.Date. Any book written by him, even many > years ago, is more than readable. You can hardly do serious > development without having read most of his writings or the equivalent > by others. > > Nils.Myklebust@idg.no > NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway > My opinions are those of my company > The Informix FAQ is at http://www.iiug.org Yes, I agree with all that. There is one rule that can be useful, though. Many optimisers have difficulty with subqueries, especially nested or complex ones. So, the one rule is: use joins instead of subqueries where possible. Peter -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 Mail: Peter.Lancashire.PL1@bayer.co.uk My Internet plumbing does not allow me to mail and post news together. Sorry. All opinions are my own and not those of Bayer plc.