Slow-running query with temp table on IDS 9.20

IDS 9.20/7.31 on HP-UX 11 The query listed below executes in about 16 seconds on IDS v7.31. The query plan does sequential scans on the product and user tables to return the values. In IDS 9.20 it takes about 301 seconds. It's easy to see why; a sequential scan is done on the temporary table tmphier (which actually has 627 rows) as its start point, that does nested loop joins to retrieve the remaining rows. Changing OPTCOMPIND from the default 0 to 1 or 2 will not change this. If make tmphier a "permanent" table the result is identical. If however I run update statistics medium on tmphier(hierid5) the query path is altered completely and the query whizzes through in about 6 seconds. Why should IDS 9.20 behave so differently? And is there anything I can do about it, given that it;s impractical to issue update statistics on temporary tables via the VisualBasic application? thanks Neil ---------------------------------------------------------------------------- ------------------------------------ SELECT DISTINCT P.ProductID FROM Product P, RetailUnit R, Supplier S, User U, Hierarchy H, TmpHier TH WHERE P.RetailUnitID = R.RetailUnitID AND P.SupplierID = S.SupplierID AND P.ProductID = H.ProductID AND H.HierLevelID = 6 AND H.HierFromDate = '2000-09-18' AND P.TCUserID = U.UserID AND P.ProductRoutingFlag = 1 AND P.InCatalogue = 'Y' AND '2000-09-25' BETWEEN P.CatalogueStartDate AND P.CatalogueEndDate AND H.HierParentID = TH.HierID5

Oh! 16 second down to 6 with IDS 2000!! Surely you can prepare and execute an update stats in the VB application?? We do so in 4gl applications! Neil Truby wrote in message <8pm3b3$h3c$1@lyonesse.netcom.net.uk>... >IDS 9.20/7.31 on HP-UX 11 > >The query listed below executes in about 16 seconds on IDS v7.31. The query >plan does sequential scans on the product and user tables to return the >values. > >In IDS 9.20 it takes about 301 seconds. It's easy to see why; a sequential >scan is done on the temporary table tmphier (which actually has 627 rows) as >its start point, that does nested loop joins to retrieve the remaining rows. >Changing OPTCOMPIND from the default 0 to 1 or 2 will not change this. > >If make tmphier a "permanent" table the result is identical. If however I >run update statistics medium on tmphier(hierid5) the query path is altered >completely and the query whizzes through in about 6 seconds. > >Why should IDS 9.20 behave so differently? And is there anything I can do >about it, given that it;s impractical to issue update statistics on >temporary tables via the VisualBasic application? > >thanks >Neil > >--------------------------------------------------------------------------- - >------------------------------------ >SELECT DISTINCT P.ProductID FROM Product P, RetailUnit R, Supplier S, User > U, Hierarchy H, TmpHier TH WHERE P.RetailUnitID = R.RetailUnitID AND > P.SupplierID = S.SupplierID AND P.ProductID = H.ProductID AND > H.HierLevelID = 6 AND H.HierFromDate = '2000-09-18' AND P.TCUserID = > U.UserID AND P.ProductRoutingFlag = 1 AND P.InCatalogue = 'Y' AND > '2000-09-25' BETWEEN P.CatalogueStartDate AND P.CatalogueEndDate AND > H.HierParentID = TH.HierID5> >

Would a directive help??? Doug "Neil Truby" <ntruby@netcomuk.co.uk> wrote in message news:8pm3b3$h3c$1@lyonesse.netcom.net.uk... > IDS 9.20/7.31 on HP-UX 11 > > The query listed below executes in about 16 seconds on IDS v7.31. The query > plan does sequential scans on the product and user tables to return the > values. > > In IDS 9.20 it takes about 301 seconds. It's easy to see why; a sequential > scan is done on the temporary table tmphier (which actually has 627 rows) as > its start point, that does nested loop joins to retrieve the remaining rows. > Changing OPTCOMPIND from the default 0 to 1 or 2 will not change this. > > If make tmphier a "permanent" table the result is identical. If however I > run update statistics medium on tmphier(hierid5) the query path is altered > completely and the query whizzes through in about 6 seconds. > > Why should IDS 9.20 behave so differently? And is there anything I can do > about it, given that it;s impractical to issue update statistics on > temporary tables via the VisualBasic application? > > thanks > Neil > > -------------------------------------------------------------------------- -- > ------------------------------------ > SELECT DISTINCT P.ProductID FROM Product P, RetailUnit R, Supplier S, User > U, Hierarchy H, TmpHier TH WHERE P.RetailUnitID = R.RetailUnitID AND > P.SupplierID = S.SupplierID AND P.ProductID = H.ProductID AND > H.HierLevelID = 6 AND H.HierFromDate = '2000-09-18' AND P.TCUserID = > U.UserID AND P.ProductRoutingFlag = 1 AND P.InCatalogue = 'Y' AND > '2000-09-25' BETWEEN P.CatalogueStartDate AND P.CatalogueEndDate AND > H.HierParentID = TH.HierID5> >