Sqlbase: Delete rows in large table costs hours .....

forum.gupta.bugreport (2005-2010)
michael
Germany
Posts: 280
Joined: 07 Jun 2018, 15:13
Location: Stuttgart, GER

Sqlbase: Delete rows in large table costs hours .....

Post by michael » 21 Apr 2005, 19:18

 Posted by:  Michael Hummel 

select count(*) from table1
957934 Rows

set recovery off
recovery is off

delete from table1
-> now it runs 2 hours !!!

Intel Xeon 2,4 GHz
SQLBase 8.5 PTF3
Database Size 690 MB

no one else is connected to the Server
normal execution of the sql-statements is very good

my wish: truncate table

thx
michael

Jim McNamara

Re: Sqlbase: Delete rows in large table costs hours .....

Post by Jim McNamara » 21 Apr 2005, 22:27

 Posted by:  Jim McNamara 

Try:

set recovery off;
lock database;
delete from table;
commit;
set recovery on;
unlock database;

Of course, this presumes you are the only one logged on.

Frank Boettcher (ITG)

Re: Sqlbase: Delete rows in large table costs hours .....

Post by Frank Boettcher (ITG) » 21 Apr 2005, 22:46

 Posted by:  Frank \(ITG\) 

Also try to drop and recreate indexes after deletion.

Best regards,
Frank Boettcher
Ice Tea Group, LLC

Mike Vandine

Re: Sqlbase: Delete rows in large table costs hours .....

Post by Mike Vandine » 21 Apr 2005, 23:19

 Posted by:  Mike Vandine 

Drop the indexes *before* the delete and create them afterward. Better
still, drop the table and recreate it instead of deleting all rows.

Mike

Frank Boettcher (ITG)

Re: Sqlbase: Delete rows in large table costs hours .....

Post by Frank Boettcher (ITG) » 21 Apr 2005, 23:26

 Posted by:  Frank \(ITG\) 

Mike,

this is what I was trying to say, but thanks for clarifying my vague
statement.

In fact I had made mixed experiences with drop table.
Sometimes the drop table did not work and I suspect it was due to some
locks on system tables being held. In such a case, the delete might be a
slower
but safer bet than a drop table.

Frank

michael
Germany
Posts: 280
Joined: 07 Jun 2018, 15:13
Location: Stuttgart, GER

Re: Sqlbase: Delete rows in large table costs hours .....

Post by michael » 22 Apr 2005, 09:10

 Posted by:  Michael Hummel 

thanks for your comments,

@Jim:
i have set recovery off and no one else was connected to the database (job
runs overnight)
so, does locking the database speed up the process ??

@Frank:
dropping the table is not the thing i want. its not our database, it comes
with a third party application (employee management, hr). dropping means to
rebuild all indexes, all insert/update triggers and a lot of security
(grants).

i cant understand, that deleting a row costs hundreds of times more than
inserting with load.
this morning the job comes to an end: This means one million rows in nealry
6 hours
957,934 ROWS DELETED
IN 20,682.31 SECONDS

46.12 ROWS PER SECOND / 0.02 SECONDS PER ROW

Compare: when i do the same with sqlserver 2000: 1 second (with truncate
table), 6 minutes (with delete from table)

Jim McNamara

Re: Sqlbase: Delete rows in large table costs hours .....

Post by Jim McNamara » 22 Apr 2005, 16:39

 Posted by:  Jim McNamara 

Locking speeds up a load a hundred fold. I presume that it will give
similar results on bulk type processes. -- Speaking of BULK, it might also
help to turn that on.

The reason a load is faster - it inserts all the rows in a table, then
builds the indexes. The delete updates all the indexes for each row that is
deleted.

kwkonrad
Germany
Posts: 290
Joined: 24 Jan 2018, 13:10
Location: Karlsruhe Germany

Re: Sqlbase: Delete rows in large table costs hours .....

Post by kwkonrad » 23 Apr 2005, 10:44

 Posted by:  Klaus-Werner Konrad 

And one additional thought:

If you delete a row, the RDBMS has to check the contrains,
to see if the deleted row is referenced from another table.

If you insert a row it logically cannot be referenced so far
by any other table ...

Just my 2 cents :-)

Klaus-Werner Konrad

Bernhard Weber

Re: Sqlbase: Delete rows in large table costs hours .....

Post by Bernhard Weber » 25 Apr 2005, 09:20

 Posted by:  Bernhard Weber 

Hi Michael,

one further experience:

deleting a table in Parts seems to be at least a little bit better:

DELETE FROM WHERE
commit ;
DELETE FROM WHERE
commit ;

... in reasonable parts of about 10000 - 100000 rows
DELETE FROM WHERE
commit ;

seems to speed up a little bit.

the time to delete a number of rows
is not proportinal to the number of rows to delete.

HTH Bernhard

michael
Germany
Posts: 280
Joined: 07 Jun 2018, 15:13
Location: Stuttgart, GER

Re: Sqlbase: Delete rows in large table costs hours .....

Post by michael » 03 May 2005, 15:11

 Posted by:  Michael Hummel 

thank you for your comments and your help.
michael

Return to “gupta.bugreport”

Who is online

Users browsing this forum: [Ccbot] and 0 guests