slow delete operation - any idea anyone please
A user on IDS 11.50.UC9 (Linux) asked why a batch DELETE (deleting 20-30k rows at a time from a 'parcels' table by objectid range) was slow, and why OAT showed estimated rows far higher than actual. Replies: estimate/actual gaps usually mean stale or insufficiently detailed data distributions (update statistics); Art Kagel suggested his dbdelete utility (ESQL/C, needs compiling, from utils2_ak) as faster than a plain range DELETE; others pointed to transaction logging overhead, delete triggers/referential constraints, unload-and-reload instead of mass delete, and John Miller noted detached indexes plus enough btree scanner threads with alice level 6+. The poster opened a case with IBM support; no resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IBM Informix Dynamic Server Version 11.50.UC9
Linux wldbp655 2.6.18-348.6.1.el5 #1 SMP Fri Apr 26 09:21:26 EDT 2013 x86_64
x86_64 x86_64 GNU/Linux
we are running a delete query that takes far longer than we expect
using oat trace i see this
Estimated Cost Estimated Rows Actual Rows SQL Error ISAM Error Isolation
49180 159193 20000
the query
delete from parcels where objectid < 1700000 "
anyone know why the Estimated Rows should be more than actual rows ?
The query is being run deleting 20,000 30,000 rows at a time each estimate is
diffrent and much higher than rows deleted
Differences between estimated and actual rows are almost always due to data
distributions being insufficiently detailed or outdated.
FYI, deleting very large numbers of rows using a simple DELETE statement,
especially one covered by a range of keys or inequality on a key typically
does not perform as well as one might expect. Give my dbdelete utility a
try. The algorithms that it uses for this type of delete will frequently
outperform such a DELETE statement.
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, Jul 14, 2013 at 10:19 PM, KARL OLIVER <karl.oliver@maf.govt.nz>wrote:
> IBM Informix Dynamic Server Version 11.50.UC9
> Linux wldbp655 2.6.18-348.6.1.el5 #1 SMP Fri Apr 26 09:21:26 EDT 2013
> x86_64
> x86_64 x86_64 GNU/Linux
>
> we are running a delete query that takes far longer than we expect
> using oat trace i see this
>
> Estimated Cost Estimated Rows Actual Rows SQL Error ISAM Error Isolation
>
> 49180 159193 20000
>
> the query
> delete from parcels where objectid < 1700000 ">
> anyone know why the Estimated Rows should be more than actual rows ?
>
> The query is being run deleting 20,000 30,000 rows at a time each estimate
> is
> diffrent and much higher than rows deleted
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c365a638f7a804e1840266
Art does your dbdelete ran out the box as it were i does it need compiling ?
It is ESQL/C code, so it does need to be compiled. I no longer have a copy of the CSDK v3.50, so I couldn't supply a binary for you. If you don't have a compiler/CSDK on your production machine, you can compile it on a development box and copy the binary over to production or run it remotely from development if that machine has access to the production server. Dbdelete is part of my utils2_ak package. 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, Jul 14, 2013 at 11:01 PM, KARL OLIVER <karl.oliver@maf.govt.nz>wrote: > Art > does your dbdelete ran out the box as it were i does it need compiling ? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1133e4143d596e04e1842c12
Can you share the table schema and the query plan? Beside that, does it
contain delete triggers or is it referenced by other tables?
On Jul 15, 2013 3:20 AM, "KARL OLIVER" <karl.oliver@maf.govt.nz> wrote:
> IBM Informix Dynamic Server Version 11.50.UC9
> Linux wldbp655 2.6.18-348.6.1.el5 #1 SMP Fri Apr 26 09:21:26 EDT 2013
> x86_64
> x86_64 x86_64 GNU/Linux
>
> we are running a delete query that takes far longer than we expect
> using oat trace i see this
>
> Estimated Cost Estimated Rows Actual Rows SQL Error ISAM Error Isolation
>
> 49180 159193 20000
>
> the query
> delete from parcels where objectid < 1700000 ">
> anyone know why the Estimated Rows should be more than actual rows ?
>
> The query is being run deleting 20,000 30,000 rows at a time each estimate
> is
> diffrent and much higher than rows deleted
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec50408da045faf04e1884314
I have logged a call with IBM support over this
Another reason it could be running slow is because of transaction logging.
.. Also, when you delete rows from a table, those rows are flagged as deleted but not physically removed from the datafile, so if you're going to delete a large number of rows from the table, it might be worthwhile to unload only the rows you want to keep, drop/re-create the table, load the rows back in and re-create the indexes.
Deletes will run faster if the indexes are detached. In general this is the case unless you have upgraded from version 7 and not rebuilt your indexes since the conversion. Also you need to ensure that you have enough btree scanner threads as they clean the index removing dirty items so one does not have to search over these unwanted items. Also make sure you have the "alice" parameter in the btree scanner set to 6 or higher (this is the default) John F. Miller III STSM, Lead Architect miller3@us.ibm.com ids-bounces@iiug.org wrote on 07/15/2013 07:11:11 PM: > From: "FRANK DEVELOPER" <frankcomputer@ymail.com> > To: ids@iiug.org, > Date: 07/15/2013 07:13 PM > Subject: Re: slow delete operation - any idea anyone please [30844] > Sent by: ids-bounces@iiug.org > > ... Also, when you delete rows from a table, those rows are flagged > as deleted > but not physically removed from the datafile, so if you're going to delete a > large number of rows from the table, it might be worthwhile to > unload only the > rows you want to keep, drop/re-create the table, load the rows back in and > re-create the indexes. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >