WHERE prodcode in (NULL, '', 'DIALR', 'TRAVL','HOTLN', 'PWFAX',
'DEBIT') and access LIKE '000%' and
canceldate > TODAY
The first time a user runs the report we have a timeout. After the first select that errors all other users reports are fine. If I do a simple select through dbaccess and the user runs the report for the first time it works fine. If no one runs the report for a hour we run into the situation again on the first select. Any Idea's?
Informix caches the data in the memory, which is why it's faster the 2nd time around. It goes to the Least Recently Used portion when nobody uses the data set for a while... then later on it is released from memory, making space for new recently executed queries.
You might look at the explain output of running this query and determine what the select goes through to satisfy the query. I suspect the LIKE and/or the "> TODAY" are causing table scans of (perhaps) a large table?
If this is the case you need to look at either applying some indexes that would help the query or rewriting the query to use a different approach.
Is this data static during the day? Create a summary table that you query.
Do you need the wildcard - that's USUALLY a killer.