Fragmentation and ESQL

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. Our first suggestion was to use SELECT FIRST 30 (since the app doesn't really want all 34K rows anyway). In dbaccess, the performance improved from just under 4 minutes to just under one half of one second. Great. But in ESQL, we weren't so lucky. Performance went from just over eight minutes to just under seven. We only saved a minute. Not near enough on an OLTP application. After much research, we figured out that ESQL was indeed returning only 30 rows despite taking as long as it did. More detailed examination of the query plan showed us that when run directly in dbaccess, the optimizer used fragment elimination to narrow the search (of both the index and the table) down to one fragment. ESQL searched all fragments, and this accounted for the dramatic time differential even without FIRST 30. As it turns out, when using PREPARE in ESQL, the statement is optimized at PREPARE time. Which means that the value of col1 is '?' as far as the optimizer's concerned, so it doesn't use fragment elimination. Solution: Use "OPEN cur USING x,y,z WITH REOPTIMIZATION" which, according to the documentation, will repotimize the statement before opening it, right? Wrong. It appears as though the WITH REOPTIMIZATION is done prior to doing the USING substitution, so there's no perceptible performance difference. Thus far, our ONLY option has been to re-PREPARE the statement each time, right before OPENing it, which defeats much of the purpose of using PREPARE in the first place. We are currently in the process of contacting Informix Technical Support to see if this is a bug or (please, no!) 'expected behavior.' The above in short form: In ESQL, fragment elimination doesn't work with OPEN USING. So beware when writing ESQL programs. If you'd like more details, please contact me at 'tgirsch@iname.com' and I'll be glad to drill down on our testing methods and results. Respectfully, - Thomas J. Girsch Database Systems Manager Arch Communications Group, Inc.