Re: optimizing - sqexplain

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