Re: Speeding up reports, general processing in 4GL?

Mancunian1@aol.com wrote: : Informix 4GL 6.0, Online 5.0, SCO Unix. : I've long been interested in the subject of speeding up reports / 4GL : processing in general. : Anyone want to comment on the general subject? : There are some obvious things like the creation of indexes. : What about the not so obvious?, for example. : 1. Because a cursor is not being constantly re-declared,should the following : construct be faster ? : declare l_a record like a.* : declare l_b record like b.* : declare cursor cursa for select * from table a : declare cursor cursb for select * from table b where b.keyfield = : l_a.keyfield : foreach cursa into l_a.* : foreach cursb into l_b.* : # code : end foreach : end foreach : (this works, by the way!). : as opposed to : : declare cursor cursa for select * from table a : foreach cursa into l_a.* : declare cursor cursb for select * from table b where b.keyfield = : l_a.keyfield : foreach cursb into l_b.* : # code : end foreach : end foreach Absolutely, your 1st example will work faster than the 2nd, but quite likely even faster would be your part 4. example. See my comment there. : 2. Would the above (top example) be improved by preparing the cusor(s)? Not particularly. You can improve performance if you PREPARE cursors with placeholders ("SELECT * FROM a WHERE keyfield = ?"), but this doesn't apply to your example. : 3. If an SQL is executed more than once it is best to prepare it (I hope?). Yes. : If the equivalent code was placed in a stored procedure would it be faster : than the prepared version? Not particularly. SPLs have performance hits of their own. David "Mr. SPL" Berg's rule of thumb is that a stored procedure with 3 or more SQL statements within will be faster than running the 3 SQLs in the 4GL code. Any less than than and the SPL overhead defeats any performance benefits. : 4. Are there any rules for choosing the following construct, as opposed to : the example in 1. above? : declare l_a record like a.* : declare l_b record like b.* : declare cursor cursab for : select * from table a, outer b : where a.keyfield = b.keyfield : foreach cursab into l_a.*,l_b.* : # code : end foreach You can get away with 1/2 dozen or so joins before worrying about breaking a select into seperate cursors to help performance. Make sure keyfield for both tables is indexed. : 5. Is it better to ORDER BY in the report or the SELECT (and thus use ORDER : external by)? Almost always, ORDER BY in the SELECT is better. The report creates a temp table for sorting if ORDER BY (as opposed to ORDER EXTERNAL BY) is in the REPORT function. : 6. Does moving to the 7.1 engine have any bearing on answers to the above? Not really. There will be performance benefits going to 7.1, but the way the code is written doesn't change. : Please feel free to expand on this list, any tips on improving performance : would be greatly appreciated. Avoid dense OR conditions if possible: WHERE a = "B" OR a = "C" will be slower than WHERE a IN ("B","C") Sometimes a UNION will work better than an OR as well. Index all joined and ORDER BY columns. Index most columns included in WHERE criteria. WHERE datecol >= "this/date" AND datecol <= "that/date" will be slower than WHERE datecol BETWEEN "this/date" AND "that/date" ======================================================================= Dennis J. Pimple dennisp@informix.com Opinions expressed Principal Consultant -------------------- are mine, and do not Informix Software Inc Voice: 303-850-0210 necessarily reflect Denver Colorado USA Fax: 303-779-4025 those of my employer.