delete from table

[Rami Ojares / PDF-Comics Oy <ra...@absinth.fi>]

Hi,

It seems that deleting a table takes a VERY long time.
I am importing data into derby.
And as I work with my import script I have to do the import MANY times.
So in the beginning of my import I first delete everything from the 
tables I have previously imported into.

It seems that deleting takes at least as long as doing the import.

So my question is twofold.
1) Why is that so?
2) Are there any plans to support a truncate or similar command (like in 
mysql) where the delete just wipes everything away from the table 
without making all kinds of checks?

- rami

Re: delete from table

[Rami Ojares / PDF-Comics Oy <ra...@absinth.fi>]

Thank you all for help and answers.

I can't use the import procedure's replace parameter, because I import 
from another db a and do elaborate transformations to the data on the way.

Dropping tables is faster.
I have this sql file where I drop all triggers, views and tables in 
proper order to drop the whole schema.
But I always have to run it twice after an import.
At some point (I am not sure if it changes or is always the same) the 
execution of those sql statements hangs for some time
and then finally returns with an exception:
"A lock could not be obtained within the time requested".
Then when I start it again at the point where the exception occurred 
everything goes fine (and fast).

Even with this one complication it is much faster to drop the schema 
than do DELETE FROM TBL for 5 tables in the schema.
(One of those tables has over 10 000 images in them but I don't think 
that's causing the problems any more than the other tables.)

I won't start using debug version just because I wish to minimize all 
the admin hassles.
And I wouldn't know where to look for the syntax (and I won't go digging 
source for a problem like this).
But I voted for issue DERBY-268.
And JIRA said it has been approved as part of sql standard.
Although I don't really know why derby community holds compliancy in 
such a high regard.
None of the databases I have ever worked with are really - in practice - 
compatible.
But I have to admit that IBM databases (DB2 and Derby) are the most 
meticulously documented.
And that's a big plus.
And maybe compliancy keeps derby somehow defined and brings clear 
guidelines to what can be included and what not.
Oh, but I digress.

What I really meant to say was thank you for the advice.

- rami

Re: delete from table

[Rami Ojares / PDF-Comics Oy <ra...@absinth.fi>]

> If this is a feature you would like to see in Derby, you should vote for
> it to give it more visibility. Or even better, volunteer to complete the
> implementation! :)
>   

Well, I already volunteered to add comments to columns and tables and 
add the boolean type to derby.
None of these have I even considered yet because I am desparately trying 
to get the software working
for which I am paid for. So I quess I better not volunteer anymore ;-)

- rami

Re: delete from table

[Rami Ojares / PDF-Comics Oy <ra...@absinth.fi>]

One observation about what makes "DELETE FROM TBL" slow.

I have two tables

CATEGORY that holds 10 rows.

ABSTRACT_PROGRAM that holds 20000 rows each pointing with a foreign key 
to category table where it is defined that ON DELETE SET NULL

If I delete CATEGORY table first and then ABSTRACT_PROGRAM deleting the 
CATEGORY table (=10 rows) takes over 30 minutes.
This is caused somehow because the ABSTRACT_PROGRAMs category references 
have to be set to null.

If I do it the other way around deleting those two tables takes 
something like 30 SECONDS!!!

It is beyond me why it takes so long to set the CATEGORY attributes to 
null in ABSTRACT_PROGRAM table.

It becomes even more mysterious because there is another table called 
IMAGE that contains images.
ABSTRACT_PROGRAM table also refers to that table with a foreign key 
constraint.
But deleting IMAGE table does not take that long at all.

The only difference between these relations is that Many 
ABSTRACT_PROGRAMs point to ONE CATEGORY
whereas ONE ABSTRACT_PROGRAM points to ONE IMAGE.
this might explain it.

- rami

Re: delete from table

[Knut Anders Hatlen <Kn...@Sun.COM>]

Rami Ojares / PDF-Comics Oy <ra...@absinth.fi> writes:

> Hi,
>
> It seems that deleting a table takes a VERY long time.
> I am importing data into derby.
> And as I work with my import script I have to do the import MANY times.
> So in the beginning of my import I first delete everything from the
> tables I have previously imported into.
>
> It seems that deleting takes at least as long as doing the import.
>
> So my question is twofold.
> 1) Why is that so?
> 2) Are there any plans to support a truncate or similar command (like
> in mysql) where the delete just wipes everything away from the table
> without making all kinds of checks?

There is an undocumented TRUNCATE command available in debug builds of
Derby, but not in production builds. There is also a request for
TRUNCATE in the issue tracker, see
https://issues.apache.org/jira/browse/DERBY-268.

If this is a feature you would like to see in Derby, you should vote for
it to give it more visibility. Or even better, volunteer to complete the
implementation! :)

-- 
Knut Anders

Re: delete from table

[Bryan Pendleton <bp...@amberpoint.com>]

> It seems that deleting a table takes a VERY long time.

As an alternative, you can try DROP TABLE and then CREATE TABLE,
and see if that is faster.

thanks,

bryan

Re: delete from table

[Daniel John Debrunner <dj...@apache.org>]

Rami Ojares / PDF-Comics Oy wrote:
> Hi,
> 
> It seems that deleting a table takes a VERY long time.
> I am importing data into derby.
> And as I work with my import script I have to do the import MANY times.
> So in the beginning of my import I first delete everything from the 
> tables I have previously imported into.
> 
> It seems that deleting takes at least as long as doing the import.

The import procedures have a replace parameter which performs truncation 
of the table before the import. That will be much quicker than the delete.

Dan.