Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    Allentown pa
    Posts
    41

    Unanswered: View select slow first time then fast

    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
    Jeff Wain

  2. #2
    Join Date
    Nov 2002
    Posts
    11
    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.

  3. #3
    Join Date
    Aug 2003
    Location
    Allentown pa
    Posts
    41
    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
    Jeff Wain

  4. #4
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    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.
    Fred Prose

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •