Delete statement very slow - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Delete statement very slow

Author

Topic

Dieterm
Starting Member

6 Posts

Posted - 2005-12-12 : 08:58:40

Hi,

I've got a table with about 500 000 records and growing monthly by about 40 000 records

When I perform the following query:

DELETE from [myTable] WHERE Month = '07' AND Year='2005'

This query will take about 10 minutes to execute. Columns Month & Year are both indexed.

Surely MSSQL can't be this slow on only 500 000 records.

Must I do some other database optimization ???

Thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-12 : 09:00:14

How many indexes are on this table? How wide are the columns (average width)? What do your disk queue lengths look like when this operation is occurring? What about processor and CPU? SQL Server COULD do it a lot quicker.

The questions is really can your installation do it quicker.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-12 : 10:03:23

Slow deletes can also be caused by other tables that have foreign keys to the table performing the delete. If there are other tables that reference this table, make sure those (referencing) columns are indexed as well.

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-12 : 10:10:55

There are a number of things that could be slowing it down. The first thing you should do is check the query plan in Query Analyzer to see what it shows.

It you have delete triggers on the table, that could be slowing it down.

It there are tables that reference the table you are deleting from, that could slow it down, especially if the tables with the foreign keys do not have indexes on the foreign keys columns. It could be even worse if they are set for cascading deletes, because you could be deleting a lot more rows than you think.

I doubt that the indexes on columns Year and Month are even being used, since they would have very low selectivity. Year would only have two or three distinct values in it, and month would only have 12. You would probably be better off dropping them to save the CPU overhead and space.

CODO ERGO SUM

Dieterm
Starting Member

6 Posts

Posted - 2005-12-13 : 01:01:46

HI all,

What do you mean by delete triggers ?
There are no foreign keys linked to this table.

quote:
How wide are the columns (average width)?

Ho do I check this ?

Any other ideas ?

Thanks for your replies.

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 02:17:57

"What do you mean by delete triggers ?"

SQL code, "attached" to a table, that is executed when a record is deleted. This can be used, for example, to delete records in associated tables, or to "copy" the deleted records to Audit tables, and so on. These increase the "work load" for a Simple Delete

"Ho do I check this ?"

Posting the DDL here for CREATE TABLE of your table would do.

"Any other ideas ?"

Yeah, provide the answers to the requests the good folk have given you above!:

How many indexes are on this table?

What do your disk queue lengths look like when this operation is occurring?

What does the Query Plan show?

Kristen

Dieterm
Starting Member

6 Posts

Posted - 2005-12-13 : 04:52:47

OK thanks.
sorry somewhat new to MSSQL.

There is no Delete triggers on this table.

quote:

Posting the DDL here for CREATE TABLE of your table would do.

How do I go about doing this from Enterprise Manager ?
How do I check the:

quote:
disk queue lengths

?

I'm not to sure how to interpret the Execution Plan.

I got this actually to be somewhat faster, by removing alot of indexes and to remove the clustered index.
I currently have only 3 indexes plus one primary key (ID key of the table)

Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-13 : 05:22:41

"...the DDL here for CREATE TABLE of your table would do."...
in EM, right-click on a table and follow the prompts for 'create script'...

the output is the DDL which basically is the instructions to create a table and all related items (indices, etc)

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 06:09:23

"I'm not to sure how to interpret the Execution Plan."


SET SHOWPLAN_TEXT ON
GO
BEGIN TRANSACTION
GO
-- ... put query here - e.g.:

SELECT * FROM Northwind.dbo.Products

GO
SET SET SHOWPLAN_TEXT OFF
GO
ROLLBACK
GO

You should be able to just try the one above, and then put your DELETE statement in instead of my SELECT ... stuff. The BEGIN TRANSACTION / ROLLBACK will reverse your delete, and not actually run it [note that this may make it take TWICE as long as at present, don't worry about that for now!], so you'll get the chance to do it again with an "improved query" once we've worked out what that will be!

"remove the clustered index"

That may not be a good idea from the perspective of SELECT stuff, but lets see the DDL (and tell us what the clustered index was if its no longer there please!)

Kristen

Dieterm
Starting Member

6 Posts

Posted - 2005-12-13 : 07:56:24

I removed the clustered index from column Id
alos I've tried the code provided by Kristen as follows:

SET SHOWPLAN_TEXT ON
GO
BEGIN TRANSACTION
GO
delete from CalcCalls where SubMonth='08' and SubYear='2005'
GO
SET SHOWPLAN_TEXT OFF
GO
ROLLBACK
GO

, but got the following error:

Server: Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Here's the create table layout:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CalcCalls]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CalcCalls]
GO

CREATE TABLE [dbo].[CalcCalls] (
	[Id] [int] IDENTITY (0, 1) NOT NULL ,
	[ChargeCode] [varchar] (50) NULL ,
	[SubMonth] [char] (2) NULL ,
	[SubYear] [char] (4) NULL ,
	[SubmissionDate] [varchar] (50) NULL ,
	[SubmissionTime] [varchar] (50) NULL ,
	[Duration] [int] NULL ,
	[CellDuration] [int] NULL ,
	[ChargeType] [varchar] (50) NULL ,
	[Distance] [varchar] (50) NOT NULL ,
	[ToAddress] [varchar] (50) NULL ,
	[Cost] [float] NULL ,
	[CellCost] [float] NULL ,
	[ItemsRecv] [int] NULL ,
	[ItemsSent] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CalcCalls] ADD 
	CONSTRAINT [PK_CalcCalls] PRIMARY KEY  NONCLUSTERED 
	(
		[Id]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_ChargeCode] ON [dbo].[CalcCalls]([ChargeCode]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_SubYear] ON [dbo].[CalcCalls]([SubYear]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_SubMonth] ON [dbo].[CalcCalls]([SubMonth]) ON [PRIMARY]
GO

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 08:32:27

"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION"

Sorry, the "SET SHOWPLAN_TEXT ON" does not actually execute the code, it just outputs the query plan. Sp you need to leave the BEGIN TRANSACTION and ROLLBACK out.

However, when you want to try the DELETE (e.g. to test the performance of a change you are trialling) you might want to put those back in so you can have several goes without actually deleting anything.

Once you've sorted than muck-up of mine out

please post the Query Plan for us to look at.

Your table is "small" in size, so the delete should be quick.

Your indexes IX_SubYear and IX_SubMonth are pointless, as they are not going to be used.

Having a composite clustered index on SubYear, SubMonth will help - and probably boost performance significantly if your DELETE is of 10% of the records in the table, or less.

What does:

SELECT COUNT(*), COUNT(DISTINCT ChargeCode) FROM dbo.CalcCalls

give you?

If "COUNT(*)" is more than, say, 5 times "COUNT(DISTINCT ChargeCode)" then your index "IX_ChargeCode" is probably not going to be used.

Kristen

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-13 : 08:40:39

Hello Dieter,
Hoe gaan dit daar in Pretoria?

Dieter - is the id Column of this CalcCalls table been referenced in any other table with a foreign key constraint - because like TG mentioned if this is the case then if these tables aren't indexed on that field too - it could cause performance issues.

Does the query run quickly if you just select say the id into a temp table with the same filter
eg:
SELECT
ID
INTO
#Temp
FROM
[dbo].[CalcCalls]
WHERE
SubYear = '2005' AND
SubMonth = '07'
--****************************************************************************

If this runs relatively quickly then maybe you can then delete from the table by joining on the ID (primary key field) with this newly created temp table.

--*****************************************************************************
The other thing is - why are you deleteing an entire months worth of data?
Is it because you want to only keep a window of a few months worth of data? - If this is the case have you thought of partitioning your tables by month for instance - this can usually boost performance quite a bit.

Duane.

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 09:12:50

Ditch: I don't think he's got any FKs ("There are no foreign keys linked to this table), and hes adding only 40,000 rows a month - which would probably make horizontal partitions a bit of a bind!

Dieterm: I forgot to ask: What sort of hardware are you running this on? We drop around 8,000,000 rows a day, and that takes about the same time as the 40,000 rows you are deleting (around 10 minutes), so maybe your hardware is not "specifically configured for SQL Server" ??

Kristen

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-13 : 09:23:53

>>("There are no foreign keys linked to this table
- oops - didn't see that - sorry.
>>hes adding only 40,000 rows a month - which would probably make horizontal partitions a bit of a bind!

Not if there is a view above the tables with an on insert trigger to decide which table the data belongs in on it ie - federated tables

Duane.

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 09:46:13

"Not if there is a view above the tables with an on insert trigger to decide which table the data belongs in on it ie - federated tables"

OK, but won't that have to be set up to handle a new table every month?

Kristen

Dieterm
Starting Member

6 Posts

Posted - 2005-12-14 : 05:48:49

ditch:
Dit gaan goed hier in Pta- van waar is jy ?, sukkel bietjie met die SQL wat te stadig is.

By selecting into a temp table it took considerbly less time.
How do I then delete from a join ?
Won't it take more time seeing that I'm now going to make more DB connections ?

The reason for deleting an entire month is because sometimes I want to recalculate the costs for the month.
I first get all the records from the live data Table name LogCalls,I then do the calculations with a scripting language, and populate this table : CalcCalls.

I really still puzzled as to why MSSQL is strugling with a small amount of records like I using currently(500 000). This will the grow by 40 000+ per month ???????

Thanks

Dieterm
Starting Member

6 Posts

Posted - 2005-12-14 : 05:57:50

Kristen:

quote:
What sort of hardware are you running this on?

This is the weird part, all of this is running on my local machine.
Windows XP Prof
Pentium 4, 3.00GHz
1GB Ram

So I really cannot understand why it should be this slow.

Kristen
Test

22859 Posts

Posted - 2005-12-14 : 07:26:40

"all of this is running on my local machine"

That's probably why its slow then. SQL really does benefit from specialist server-type hardware. That's not to say you can't optimise it and speed it up, but I don't reckon its ever going to be great on a workstation.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources