Hello,

I have a question. I hope someone can help. We do the following with a view.

CREATE VIEW serv_authcodes AS
SELECT DISTINCT account, btn, npanxx, prodcode, servclass, feeschedidx,

acctcodeflag
FROM servdef
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?

Thanks
Jeff

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.

Thanks what I did in the short term is simply have a script running to select every min. Is there any other way to force the data to stay in memory

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.