Fastest way to delete from a table?

forum.sqlbase (2000-2010)
NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

Fastest way to delete from a table?

Post by NewsgroupServer » 31 Jan 2009, 07:40

 Posted by:  F R Bhote 

When I want to delete (sometimes all, sometimes half) the rows in a table, a
simple delete statement takes forever. Dropping the table and re-creating is
not an option.

What is the fastest way to delete?

NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

Fastest way to delete from a table?

Post by NewsgroupServer » 01 Feb 2009, 04:25

 Posted by:  Mike Vandine 

Hi Feroz,

If you can't guarantee that all the rows in the table are to go, probably
the best thing to do is:

1. Drop all the indexes so the delete doesn't have to update them.
2. Pay particular attention to any index that is defined in a primary key
statement (do an unload schema and check this). If there IS a primary key,
drop the primary key.
3. Set recovery off.
4. Delete the rows required.
5. Create all the indexes and (if required) create the primary key.
6. Commit and turn recovery on.

Hope this helps.

Mike

NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

Fastest way to delete from a table?

Post by NewsgroupServer » 02 Feb 2009, 06:33

 Posted by:  F R Bhote 

Hi Mike,

I need to do this with users connected - maybe once or twice a day. Dropping
the (only) index is a possiblity, but I've noticed that with SB 11, any DDL
locks up all the tables and everyone gets timed out.

Some other way - maybe a select and delete row by row and a commit every
row - or a commit every 100 rows or something else?

Feroz

NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

Fastest way to delete from a table?

Post by NewsgroupServer » 02 Feb 2009, 09:07

 Posted by:  Dmytro G 

Hi,

Just another thought. May be it would be possible to think about deletion
beforehand? My idea is to create trigger on insert or update (or both). This
trigger would launch some stored procedures. And the procedure will check
for rows to delete and if necessary delete them. Using such a technique you
delete rows by small portions and it will not be so time consuming.

Regards, Dmyto.

Return to “forum.sqlbase”

Who is online

Users browsing this forum: Ccbot [Crawler] and 0 guests