help: optimzer directives being overridden?!

Hello, This is my first posting to this newsgroup. My name is Han, and I work at an informix shop with HP9000/Hp-Unix that uses informix 7.3 Dynamic Server. I've been working on ESQL/C applications for the past year, but have been programming for many years before that. Any ideas and help on this problem would be deeply appreciated. One of our applications uses table S as a list of tasks for workflow scheduling, so there are many inserts and deletions on S every day. Typically table S will have approx 500,000 rows. There are about 9 fields in S (S.s1 .. S.s9 ) that determine a unique task and a unique row in S. We have a named unique index on the 9 fields "xtaskid". We noticed that the application would be doing a sequential scan on table S even though it was SELECTing for a unique taskid. this was causing serious performance problems on our scheduling application. We resorted to optimizer directives : "SELECT --+ index( S \\"xtaskid\\" \\n" "FROM S"... "WHERE s1=? AND s2=? ... AND s9=? FOR UPDATE" to force the SELECT to use the xtaskid index. This has helped but I still see the select taking much longer (5-6 seconds) than expected for certain values it searches for. I suspect that the optimizer directive is being overridden somehow. The problem goes away when I kill the application and restart it: The same queries which took 5-6 seconds take several milliseconds after the restart. This SQL statement is processed by ESQL/C as so: sprintf( sql_stmnt, "SELECT --+ index ... FOR UPDATE"); EXEC SQL PREPARE sel_task_id FROM :sql_stmnt; EXEC SQL DECLARE sel_task_cur CURSOR FOR sel_task_id; ... EXEC SQL OPEN sel_task_cur USING :s1_value, ... :s9_value ; ... EXEC SQL FETCH sel_task_cur INTO .... ; The FETCH takes 5-6 seconds when this problem occurs (even when the result is SQLNOTFOUND). Has anyone experienced similar problems with optimizer directives? Note that this problem is intermittent - it only happens some times. (P.s. I know that it is not good form to use the optimizer directives especially as explicitly as we do but we are quite desperate). thank you, Han hanhwe_kim@yahoo.com Sent via Deja.com http://www.deja.com/ Before you buy.

Hello, Your problem may be caused by the erroneous buffer management. What is exactly the version of your Online-server? Before 7.30.UC7 there is a known bug: it can happen, that your database buffer contains almost only index pages. It may of course damage the performance. You can check it with following command: onstat -P | tailTypical output if error occurs: Percentages: Data 3.49 Btree 94.16 Other 2.35 In this case you should consider an update to 7.30UC7 or higher. The problem is that because of LRU priorising the index pages are not flushed out of buffer any more. The symptom The symptom seems to confirm this assumption: after a restart your query runs again with good performance. In this moment the buffer is not filled up yet with index pages. Have luck, Peter -- Peter Dzvonyar SAP-Consultant R/3 BC _______________________________________________________________ email: Peter.Dzvonyar@ops.de p.dzvonyar@t-online.de _______________________________________________________________