Re: Fragmentation and ESQL

Thomas J. Girsch wrote: > > All, this was originally going to become a question, but we figured it out, > so now it's become a general FYI. > > We've been having a performance problem involving ESQL 7.22 (C and COBOL), > IDS 7.30.UC3 and a fragmented table. The statement went something like > this: > > SELECT * > FROM tab > WHERE col1 = ? > AND col2 = ? > AND col3 = ? > ORDER BY col1, col2, col3;> > The table is fragmented by expression on col1, and there's a composite index > on col1, col2 and col3 in the order listed. > > In our particular case, running the SQL directly in dbaccess (supplying > values) took just under 4 minutes and returned some 34,000 rows. In ESQL, > the same query (using PREPARE, DECLARE and OPEN USING) took just over 8 > minutes, more than twice as long. Query plans showed that the index was > being used in both cases. [SNIP extended description] Here is the answer. In dbaccess you give the parameters actual values so that the optimizer can perform fragment elimination. In the ESQL/C program since at least one of the replaceable parameters (col1) in this query is involved in the fragmentation expression and since the optimizer does not know at PREPARE time what the values will be it cannot schedule fragment elimination so it scans all fragments and schedules a sort to make sure the rows are returned in order. There is not ORDERED MERGE code in 7.22 to use any index on col1 because of the fragmentation. Without the parameters the optimizer knows that the data MUST be in a particular fragment and that the rows will return in sorted order due to the distributed (attached) index. Solution: Upgrade to 7.24UC6 or 7.30UC2 or later and use the new FIRST ROWS optimization feature which will enable the new ORDERED MERGE code which is ONLY effective under SET OPTIMIZATION FIRST ROWS. Also these versions defer some decisions about fragment elimination until OPEN time so the problem will be less severe anyway. Also petition Informix to enable ORDERED MERGE as an optional optimizer directive for queries executed under ALL ROWS optimization. Art S. Kagel