db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Monica Ferrero <MFerr...@accelrys.com>
Subject RE: Performance deleting lots of rows from a table
Date Wed, 14 Jan 2009 14:52:51 GMT
Hi Kristian,


For what I can see in the query plan the index is being used (query plan below). I don't know
if there is any obvious information in the query plan that I'm missing. There is a number
of other queries being run, mainly system ones, I guess they are not relevant.

The delete took 38 seconds from the application code for the query plan shown.


In normal conditions the derby log is very minimal, so I don't think that would have an influence?
In any case at least the System Information does not show any spike or anything for I/O whilst
the delete takes place.

Regarding having to update the statistics, I would expect this to happen automatically...
If this is not the case so far, it is a bit of a nuisance as we don't really want DB specific
code at the application level, as it is meant to work with different databases.
If it is critical and it is recommended, I guess we would need some "if derby regenerate statistics"
after a delete, but would prefer to avoid cluttering the code. I'd be interested to know a
bit more about this so we can make a decision.


Thanks a lot for your help. Very much appreciated,

Monica


2009-01-14 14:24:26.996 GMT Thread[DRDAConnThread_5,5,main] (XID = 374750), (SESSIONID = 3),
delete from listhit where listResultId = 11567113 ******* Delete ResultSet using row locking:
deferred: false
Rows deleted = 136263
Indexes updated = 2
Execute Time = 0
        Project-Restrict ResultSet (2):
        Number of opens = 1
        Rows seen = 136263
        Rows filtered = 0
        restriction = false
        projection = true
                constructor time (milliseconds) = 0
                open time (milliseconds) = 0
                next time (milliseconds) = 0
                close time (milliseconds) = 0
                restriction time (milliseconds) = 0
                projection time (milliseconds) = 0
                optimizer estimated row count:        30341.00
                optimizer estimated cost:         9606.47

        Source result set:
                Project-Restrict ResultSet (1):
                Number of opens = 1
                Rows seen = 136263
                Rows filtered = 0
                restriction = false
                projection = true
                        constructor time (milliseconds) = 0
                        open time (milliseconds) = 0
                        next time (milliseconds) = 0
                        close time (milliseconds) = 0
                        restriction time (milliseconds) = 0
                        projection time (milliseconds) = 0
                        optimizer estimated row count:        30341.00
                        optimizer estimated cost:         9606.47

                Source result set:
                        Index Scan ResultSet for LISTHIT using index LISTHIT_LISTRESULTID_INDX
at read committed isolation level using exclusive row locking chosen by the optimizer
                        Number of opens = 1
                        Rows seen = 136263
                        Rows filtered = 0
                        Fetch Size = 1
                                constructor time (milliseconds) = 0
                                open time (milliseconds) = 0
                                next time (milliseconds) = 0
                                close time (milliseconds) = 0
                                next time in milliseconds/row = 0

                        scan information:
                                Bit set of columns fetched={0, 1, 2}
                                Number of columns fetched=3
                                Number of deleted rows visited=0
                                Number of pages visited=944
                                Number of rows qualified=136263
                                Number of rows visited=136264
                                Scan type=btree
                                Tree height=4
                                start position:
        >= on first 1 column(s).
        Ordered null semantics on the following columns:

                                stop position:
        > on first 1 column(s).
        Ordered null semantics on the following columns:

                                qualifiers:
None
                                optimizer estimated row count:        30341.00
                                optimizer estimated cost:         9606.47

-----Original Message-----
From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM]
Sent: 14 January 2009 12:08
To: Derby Discussion
Subject: Re: Performance deleting lots of rows from a table

Monica Ferrero wrote:
> Hi!
>
>
>
> I need to delete a considerable number of rows from a table, and it is
> taking too long.  The operation takes equally long from ij, so I think
> it is independent of my application code.
>
>
>
> So for instance in the case below, it took about 23 seconds to execute
> the delete statement, which was deleting 156112 rows out of 380600:
>

Hello Monica,

Just a general observation.
After you delete that many rows from a table, you should regenerate the
statistics. If the statistics are outdated, the optimizer might take bad
decisions regarding the query plan.

You can do this by compressing the table, or dropping and recreating the
index. Work is also going on to make the statistics update automatic.

Regarding your delete, you can check the query plan to see if it is
doing a table scan or using the index. You should also see how your IO
system is taking the load. For instance, you may see improved
performance by putting the log on a separate device.


Regards,
--
Kristian


[ snip ]

Accelrys Limited (http://accelrys.com)
Registered office: 334 Cambridge Science Park, Cambridge, CB4 0WN, UK
Registered in England: 2326316

Mime
View raw message