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

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