IDS Forum

Re: Informix SE 7.25 on SCO OpenServer 5: query takes a ver

Posted By: ART KAGEL, BLOOMBERG/ 65E 55TH
Date: Wednesday, 24 December 2003, at 10:04 a.m.

----- Original Message -----
To: Classics@iiug.org
At: 12/24 10:01


----- Original Message -----
To: michael@jadegroup.co.uk
At: 12/24 9:51

My guess, and several suggestions:

Make sure that the stats are up to date and at the recommended levels (see the
Performance Guide or get my dostats utility). Outdated stats can cause the
optimizer to make bad decisions for specific filter values while doing well with
other values.

This I think may be affecting the query all the time so it's a different
problem, but it may explain why you never notice the problem on the faster
machine using the faster IDS9 server. A nine table join requires the optimizer
to consider the costs of every one of the 362,880 possible query paths. On a
300MHZ processor this can be a noticeable time lag. If occassionally the
sqlexec process loses its timeslice or is swapped out for some other program or
there is some other IO going on, it could appear to be an intermittent delay.
Oops, forgot to give you the answer to this one. If you run the query under
"SET OPTIMIZATION LOW" the optimizer will consider only the most likely query
paths - reducing the optimization time considerable by only costing out 45
possible paths.

I notice filters on several columns, including a MATCHES clause with a leading
wildcard. If you are relying on the primary/foreign key indexes during the
joining then the filters may be scanning physical records and different filter
values. Even if the stats are accurate, this will take more or less time
depending on how many rows pass the join conditions and have to be reviewed by
the filters. Adding indexes that start with the join columns but also contain
the filter columns will allow the filtering (except for that MATCHES clause) to
be performed as index filters without reading data rows.

Unfortunately there are no tunables for SE. You have to tune the hardware and
the OS. Look at the disk farm, can it be made faster? Can you increase the
system buffer cache on which SE relies (perhaps after adding more memory)? It
may be that certain filter values cause more data to be read forcing other data
out of the cache. If that data is needed by another query or another task
altogether, rereading it will tie up the IO subsystems and may flush rows this
query still needs. Are the files stored on a fast filesystem or a slower (say a
journaled) FS? Etc.

Art S. Kagel

----- Original Message -----
From: Michael Mayne <michael@jadegroup.co.uk>
At: 12/24 7:25

> Hello,
> I have a query that INTERMITTENTLY takes over 30 secs to run on an old SCO
> box (Intel Pentium 2 300 MHz, RAM Unknown) running Informix SE 7.25.
> I have a Dell Latitude 500 MHz P3 laptop (0.5 GB RAM) running IDS 9.21 with
> the same dataset and the query takes a few seconds.
>
> I've included the query below for reference, but it boils down to a 9 table
> select using inner joins mainly on FK columns so I think that should be
> fine.
> The query is run by a web application inside a transaction. I've also run
> the query interactively using WinSQL Lite. Both use the Informix ODBC driver
> and the results are the same.
>
> The SE DB has logging enabled (non ansi).
>
> Are there any box / SE settings that I need to look at to ensure that the
> query runs quickly?
> It's as if there are some resources getting used up that eventually stop the
> query from running.
>
> Thanks
> Michael
>
>
> SELECT JiElement.ElementID ElementID,JiElement.Rversion Rversion,
> JiElement.PartID partID,JiElement.CodeName ElementCode,
> JiElement.SerialNumberCode ElementSNCode,JiElement.ActiveStateID
> ElementASID,
> JiPart.ActiveStateID PartASID,
> JiPart.CodeName[11,25] PartCode,JiPart.LongDesc PartLongDesc,
> JiElement.LastEventID LastEventID,JiEvent.EventTypeID
> EventTypeID,JiElement.ActiveLoanID,ElsEvent.ElemStateID as ElementStateID,
> ElsElemState.ShortDesc as ElementStateSDesc,ElsEvent.ElemSubstateID as
> ElemSubstateID,ElsElemSubstate.ShortDesc as
> ElemSubstateSDesc,ElsEvent.LocationID as LocationID,JiLocation.LongDesc as
> LocationLDesc,ElsLoan.DueBackTS,JiOrg.PartyID as OrgID,JiOrg.CodeName as
> OrgCode,JiOrg.ShortDesc as OrgSDesc
>
> FROM
> JiElement,JiPart,JiEvent,ElsEvent,ElsElemState,ElsElemSubstate,JiLocation,El
> sLoan,JiOrg
>
> WHERE
> (JiElement.LastEventID=JiEvent.EventID)
> AND (JiElement.PartID=JiPart.PartID)
> AND (JiEvent.EventID=ElsEvent.EventID)
> AND (ElsEvent.ElemStateID=ElsElemState.ElemStateID)
> AND (ElsEvent.ElemStateID=ElsElemSubstate.ElemStateID)
> AND (ElsEvent.ElemSubstateID=ElsElemSubstate.ElemSubstateID)
> AND (ElsEvent.LocationID=JiLocation.LocationID)
> AND (JiElement.ActiveLoanID=ElsLoan.LoanID)
> AND (ElsLoan.CustOrgID=JiOrg.PartyID)
>
> AND (JiElement.CodeName MATCHES "*B112266 *")
> AND (ElsEvent.ElemStateID=4)
> AND (JiElement.IsInLibrary=1)
> AND (ElsLoan.CustOrgID=34 AND ElsLoan.CustOrgID IS NOT NULL)
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.555 / Virus Database: 347 - Release Date: 23/12/2003

IDS Forum is maintained by Administrator with WebBBS 5.12.