db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Monica Ferrero <MFerr...@accelrys.com>
Subject Performance deleting lots of rows from a table
Date Wed, 14 Jan 2009 11:49:39 GMT
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:

ij> select count(*) from listhit;
1
-----------
380600

ij> delete from listhit where listresultid = 11501573;
156112 rows inserted/updated/deleted

23 seconds

The definition of the table is as follows:

create table ListHit (
    LISTRESULTID int,
    INDX int,
    HIT blob,
    TAG varchar(250),
    ATTS blob );

CREATE INDEX listhit_listresultid ON listhit(listresultid);
CREATE INDEX listhit_listresultid_indx ON listhit(listresultid,indx);

And from ij:

ij> describe listhit;
COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
LISTRESULTID        |INTEGER  |0   |10  |10    |NULL      |NULL      |YES
INDX                |INTEGER  |0   |10  |10    |NULL      |NULL      |YES
HIT                 |BLOB     |NULL|NULL|21474&|NULL      |NULL      |YES
TAG                 |VARCHAR  |NULL|NULL|250   |NULL      |500       |YES
ATTS                |BLOB     |NULL|NULL|21474&|NULL      |NULL      |YES

5 rows selected

ij> show indexes;
TABLE_NAME   |COLUMN_NAME  | NON_U&|TYPE|ASC&|CARDINA&|PAGES
----------------------------------------------------------------------------------------
LISTHIT                |LISTRESULTID          |1     |3   |A   |NULL    |NULL
LISTHIT                |LISTRESULTID          |1     |3   |A   |NULL    |NULL
LISTHIT                |INDX                           |1     |3   |A   |NULL    |NULL


Any ideas?

Thanks for your help,

Monica

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

Mime
View raw message