1
vote
0answers
72 views

Why is this query stuck if there are no waits?

I am working with a vendor's activity logging system. One of the tables has 70+ million records and is constantly increasing at several hundred records per minute. They have a utility to delete old ...
3
votes
1answer
940 views

Why is SQL Server using a clustered index scan for a self referencing FK cascade delete

I have made an example to allow me to show what my issue is: Setup: CREATE TABLE [dbo].[Test]( [TestId] [bigint] IDENTITY(1,1) NOT NULL, [ParentTestId] [bigint] NULL, CONSTRAINT [PK_Test] ...
1
vote
1answer
3k views

Very slow DELETE in PostgreSQL, workaround?

I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
15
votes
2answers
1k views

Why does DELETE leave a lingering effect on performance?

At the end is a test script for comparing the performance between a @table variable and a #temp table. I think I've set it up correctly - the performance timings are taken outside of the ...
4
votes
1answer
3k views

Why is DELETE so much slower than SELECT, then DELETE by id?

I have a fairly busy InnoDB table (200,000 rows, I guess something like tens of queries per second). Due to a bug I got 14 rows with (the same) invalid email addresses in them and wanted to delete ...
3
votes
1answer
621 views

Oracle explain for cascading delete is wrong

My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it. If I hit the explain button on a statement like this ...
12
votes
3answers
1k views

Slow deletion of records when a trigger is enabled

Thought this was solved with the link below - the work around works - but the patch doesn't. Working with Microsoft support to resolve. http://support.microsoft.com/kb/2606883 Ok so I have an ...
9
votes
4answers
9k views

Methods of speeding up a huge DELETE FROM <table> with no clauses

Using SQL Server 2005. I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is ...