Improving performance of Sql Delete

sql database sybase query-optimization database-optimization
amit picture amit · Feb 23, 2009 · Viewed 17.3k times · Source

We have a query to remove some rows from the table based on an id field (primary key). It is a pretty straightforward query:

delete all from OUR_TABLE where ID in (123, 345, ...)

The problem is no.of ids can be huge (Eg. 70k), so the query takes a long time. Is there any way to optimize this? (We are using sybase - if that matters).

Answer

Aaron Digulla picture Aaron Digulla · Feb 23, 2009

There are two ways to make statements like this one perform:

  1. Create a new table and copy all but the rows to delete. Swap the tables afterwards (alter table name ...) I suggest to give it a try even when it sounds stupid. Some databases are much faster at copying than at deleting.

  2. Partition your tables. Create N tables and use a view to join them into one. Sort the rows into different tables grouped by the delete criterion. The idea is to drop a whole table instead of deleting individual rows.

Related questions

Sybase date comparison - Correct format?

I'm pretty new to Sybase and am writing a query to return results after a specified date, and also before a specified date. MM/DD/YYYY format At the moment im doing.. SELECT * From aTable WHERE afterDate >= 08/07/2013 AND beforeDate &…

Read More
SQL SELECT INSERT INTO Generate Unique Id

I'm attempting to select a table of data and insert this data into another file with similar column names (it's essentially duplicate data). Current syntax as follows: INSERT INTO TABLE1 (id, id2, col1, col2) SELECT similiarId, similiarId2, similiarCol1, similiarCol2 FROM …

Read More
Inserting Multiple Rows in Sybase ASE

(Similar question related to SQL Server : SO Link) I know in Sql Server 2008 or above, you can insert multiple rows doing the following: INSERT INTO MyTable (Name, ID) VALUES ('First',1), ('Second',2), ('Third',3) However, it seems like this syntax DOES NOT …

Read More