There exists a query to get rid of some rows in the table according to an id area (primary key). It's a pretty straightforward query:
delete all from OUR_TABLE where ID in (123, 345, ...)
The issue is no.of ids could be huge (Eg. 70k), therefore the query requires a very long time. Can there be in whatever way to optimize this? (We're using sybase - in the event that matters).
I am wondering if parsing an IN clause with 70K products in it's a problem. Perhaps you have attempted a temp table having a join rather?
You will find two methods to make claims like that one perform:
Produce a new table and copy basically the rows to remove. Swap the tables later on (
alter table name ...) I would recommend to try it out even if it may sound stupid. Some databases tend to be faster at copying than at removing.
Partition your tables. Create N tables and employ a view to become listed on them into one. Sort the rows into different tables arranged through the remove qualifying criterion. The concept would be to drop an entire table rather than removing individual rows.
Consider running this in batches. A loop running 1000 recordsa at any given time might be considerably faster than a single query that does everything and additionally won't keep your table locked to other customers as lengthy at a time.
For those who have cascade remove (and a lot of foreign key tables affected) or triggers involved, you may want to run in even more compact batches. You will have t oexperiement to determine the best idea number for the situation. I have had tables where I needed to remove in batches of 100 yet others where 50000 labored (fortunate for the reason that situation when i was removing millions of records).
However in any even I'd put my key values which i plan to delte right into a temp table and delte after that
Can Sybase handle 70K arguments in IN clause? All databases I labored with possess some limit on quantity of arguments for
IN clause. For instance, Oracle have limit around 1000.
Are you able to create subselect rather than IN clause? Which will shorten sql. Maybe that may help for this type of large quantity of values in IN clause. Something similar to this:
DELETE FROM OUR_TABLE WHERE ID IN (SELECT ID FROM somewhere WHERE some_condition)
Removing many records could be increased with a few interventions in database, if database model permits. Here are a few methods:
- you are able to quicken things by shedding indexes, removing records and re-creating indexes again. This can eliminate rebalancing index trees while removing records.
- drop all indexes on table
- remove records
- recreate indexes
- for those who have plenty of relations for this table, try crippling constraints if you're certain that remove command won't break any integrity constraint. Remove goes considerably faster because database will not be checking integrity. Enable constraints after remove.
- disable integrity constraints, disable check constraints
- remove records
- enable constraints
disable triggers on table, for those who have any and when your company rules allow that. Remove records, then enable triggers.
last, do as other recommended - create a copy on the table that consists of rows that aren't to become erased, then drop original, relabel copy and recreate integrity constraints, if you will find any.
I'd try mixture of 1, 2 and three. If that doesn't work, then 4. If things are slow, I'd search for bigger box - more memory, faster disks.
Discover what's depleting the performance!
Oftentimes you may use among the solutions provided. But there can be others (according to Oracle understanding, so things will change on other databases. Edit: just saw that you simply pointed out sybase):
- Have you got foreign secrets on that table? Ensures the mentioning ids are indexed
- Have you got indexes on that table? It may be that droping before remove and re-creating following the remove may be faster.
- look into the execution plan. Could it be utilizing an index in which a full table scan may be faster? Or the opposite way round? HINTS may help
- rather than a choose into new_table as recommended over a create table as choose may be even faster.
But don't forget: Discover what's depleting the performance first.
When you're using DDL claims make certain you realize and accept the effects it could dress in transactions and backup copies.