Re: slow delete operation - any idea anyone please

Posted in 2013

KARL OLIVER — Mon, 26 Aug 2013 02:15:17 — source: IIUG Forums & Mailing Lists

we never found out the cause of slowness had the same problem on another table using an index created by ESRI arccatalog r20_sde_rowid_uk explain output below QUERY: (OPTIMIZATION TIMESTAMP: 08-20-2013 13:11:43) ------ delete from valuationfc where objectid < 1795000 Estimated Cost: 75783 Estimated # of Rows Returned: 371488 1) stby0813.valuationfc: INDEX PATH (1) Index Name: stby0813.r20_sde_rowid_uk Index Keys: objectid (Serial, fragments: ALL) Upper Index Filter: stby0813.valuationfc.objectid < 1795000 Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 valuationfc type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 5000 371488 5000 00:00.20 75783

↪ replying to KARL OLIVER

Art Kagel — Mon, 26 Aug 2013 02:35:20 — source: IIUG Forums & Mailing Lists

FYI your data distributions are way off. Estimated rows 731,488 actual rows 5.000! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Aug 25, 2013 at 10:15 PM, KARL OLIVER <karl.oliver@maf.govt.nz>wrote: > we never found out the cause of slowness had the same problem on another > table > using an index created by ESRI arccatalog r20_sde_rowid_uk > > explain output below > QUERY: (OPTIMIZATION TIMESTAMP: 08-20-2013 13:11:43) > ------ > delete from valuationfc > where objectid < 1795000> > Estimated Cost: 75783 > Estimated # of Rows Returned: 371488 > > 1) stby0813.valuationfc: INDEX PATH > > (1) Index Name: stby0813.r20_sde_rowid_uk > > Index Keys: objectid (Serial, fragments: ALL) > > Upper Index Filter: stby0813.valuationfc.objectid < 1795000 > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 valuationfc > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 5000 371488 5000 00:00.20 75783 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c34e6c2caccd04e4d09bf9

↪ replying to Art Kagel

KARL OLIVER — Mon, 26 Aug 2013 03:02:24 — source: IIUG Forums & Mailing Lists

Thanks Art Did try to update stats but did not make much diffrence. Since we migrated this spatial db from unix to linux we have had majior performance issues. We went from ids 9.3 to 11.5 and esri sde 9.3 to 10 and spatial.8.21.UC1 i think to spatial.8.21.UC5 . I think somethings changed that we are not picking up.