Delete Query Performance - 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 2005 Forums

Transact-SQL (2005)

Delete Query Performance

Author

Topic

abdou_kadri
Starting Member

13 Posts

Posted - 2009-05-25 : 12:32:59

Hi ALL ,

I have the following table


CREATE TABLE [dbo].[watch_application](
	[APPLICATION_NAME] [nvarchar](10) NOT NULL,
	[WATCH_TIMESTAMP] [datetime] NOT NULL,
	[QUEUE_POPULATION] [int] NULL,
	[OLDEST_FILE_TIMESTAMP] [datetime] NULL,
	[COUNTRY] [nchar](2) NOT NULL,
	[WAIT_TIME_MIN] [decimal](8, 2) NULL,
PRIMARY KEY CLUSTERED 
(
	[WATCH_TIMESTAMP] ASC,
	[COUNTRY_CODE] ASC,
	[APPLICATION_NAME] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

This is a dynamic table inserts and deletes are frequently done.
the delete process using the following query is too Slow


DELETE FROM watch_application 
WHERE watch_time <= ( 
  SELECT MAX( watch_time ) 
  FROM watch_application WITH(NOLOCK)  
  WHERE watch_time 
     IN ( SELECT TOP 1000 watch_timestamp 
          FROM watch_application WITH(NOLOCK)    
          WHERE watch_time < '2009-05-06 22:25:58.514' ) )

Is it possible for any Performance suggestion ???
Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-25 : 14:02:36

try using join or exists rather than in

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-26 : 05:17:13

I'm not sure I understand your delete statement.

am I right in thinking that:

You are deleting every row in watch_application where the watch_time is less than or equal to the highest watch time before 2009-05-06 that was entrered in the last 1000 watch_timestamps?

If so then I don't understand this part of your code


DELETE FROM watch_application 
WHERE watch_time <= ( 
  SELECT MAX( watch_time ) 
  FROM watch_application WITH(NOLOCK)  
  WHERE watch_time 
     IN ( SELECT TOP 1000 watch_timestamp 
          FROM watch_application WITH(NOLOCK)    
          WHERE watch_time < '2009-05-06 22:25:58.514' ) )

Why are you comparing watch_time against watch_timestamp here?

Maybe you should tell us what you want to delete from the table with some sample data. There will be a much more efficient way to do this

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

abdou_kadri
Starting Member

13 Posts

Posted - 2009-05-26 : 10:09:20

Thanks Charlie for your reply,
Well I just figured out that I've pasted the query here with the mistake you've mentioned
right Query:


DELETE FROM watch_application 
WHERE watch_time <= ( 
  SELECT MAX( watch_time ) 
  FROM watch_application WITH(NOLOCK)  
  WHERE watch_time 
     IN ( SELECT TOP 1000 watch_time 
          FROM watch_application WITH(NOLOCK)    
          WHERE watch_time < '2009-05-06 22:25:58.514' ) )

This query is in an application to watch the status of another App.
this table keeps track for all files run through the application. This table is getting larger and larger. This query will run whenever files are entered to delete a big portion of previous saved rows. The requirements is to delete all rows that have watch_time less then the max in the top 1000 rows that are less then a specific time.

Any Suggestion !!
Thanks In Advance

quote:
Originally posted by Transact Charlie

I'm not sure I understand your delete statement.

am I right in thinking that:

You are deleting every row in watch_application where the watch_time is less than or equal to the highest watch time before 2009-05-06 that was entrered in the last 1000 watch_timestamps?

If so then I don't understand this part of your code

DELETE FROM watch_application
WHERE watch_time <= (
SELECT MAX( watch_time )
FROM watch_application WITH(NOLOCK)
WHERE watch_time
IN ( SELECT TOP 1000 watch_timestamp
FROM watch_application WITH(NOLOCK)
WHERE watch_time < '2009-05-06 22:25:58.514' ) )

Why are you comparing watch_time against watch_timestamp here?

Maybe you should tell us what you want to delete from the table with some sample data. There will be a much more efficient way to do this

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 10:13:54

Which TOP 1000 ? There is an ORDER BY missing.

E 12°55'05.63"
N 56°04'39.26"

abdou_kadri
Starting Member

13 Posts

Posted - 2009-05-26 : 10:24:39

the top 1000 in the table
no need for ORDER BY

quote:
Originally posted by Peso

Which TOP 1000 ? There is an ORDER BY missing.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 10:25:13

Try this

DECLARE	@watch_time DATETIME

SELECT TOP 1	@watch_time = watch_time
FROM		watch_application WITH (NOLOCK)
WHERE		watch_time < '2009-05-06 22:25:58.514'
ORDER BY	watch_time DESC

DELETE
FROM	watch_application
WHERE	watch_time <= @watch_time

E 12°55'05.63"
N 56°04'39.26"

abdou_kadri
Starting Member

13 Posts

Posted - 2009-05-26 : 11:25:03

Thanks Peso for your valuable suggestion, How can we limit it for only to search in the top 1000 and not the full table
Thanks again in advance

quote:

Originally posted by Peso

Try this

DECLARE	@watch_time DATETIME

SELECT TOP 1	@watch_time = watch_time
FROM		watch_application WITH (NOLOCK)
WHERE		watch_time < '2009-05-06 22:25:58.514'
ORDER BY	watch_time DESC

DELETE
FROM	watch_application
WHERE	watch_time <= @watch_time

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 12:14:16

quote:
Originally posted by abdou_kadri

the top 1000 in the table
no need for ORDER BY

quote:
Originally posted by Peso

Which TOP 1000 ? There is an ORDER BY missing.

E 12°55'05.63"
N 56°04'39.26"

without an order by it just selects random 1000. there's no concept of first or last in table unless you explicitly specify order by means of an order by

abdou_kadri
Starting Member

13 Posts

Posted - 2009-05-26 : 12:34:36

Thanks guys,
Based on your replies I've updated my query to


DELETE FROM watch_application 
WHERE watch_time <= ( 
  SELECT TOP 1 watch_time ) 
  FROM watch_application WITH(NOLOCK)  
  WHERE watch_time 
     IN ( SELECT TOP 1000 watch_time 
          FROM watch_application WITH(NOLOCK)    
          WHERE watch_time < '2009-05-06 22:25:58.514' )
  ORDER BY	watch_time desc)

Pervious Query cost is 73%
New Query Cost is 27%

It seems when I've add the order by desc and select top 1 RUNS much faster than getting the max of the resultset.

Cheers!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 12:43:24

isnt

SELECT TOP 1 watch_time
FROM watch_application WITH(NOLOCK)
WHERE watch_time
IN ( SELECT TOP 1000 watch_time
FROM watch_application WITH(NOLOCK)
WHERE watch_time < '2009-05-06 22:25:58.514' )
ORDER BY watch_time desc)

same as

SELECT TOP 1 watch_time
FROM watch_application WITH(NOLOCK)
WHERE watch_time < '2009-05-06 22:25:58.514' )
ORDER BY watch_time desc

abdou_kadri
Starting Member

13 Posts

Posted - 2009-05-26 : 12:51:06

visakh16,
Logically it must be correct

but in my requirements, I have to capture the max in the top 1000 NOT the max of the full table

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 13:08:12

what difference does it make?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 13:39:42

quote:
Originally posted by abdou_kadri

Thanks Peso for your valuable suggestion, How can we limit it for only to search in the top 1000 and not the full table

It makes no sense.
The max of the "last" / "nearest" 1000 values (ordered descending) below a certain datetime value, will ALWAYS be the nearest lower value of that datetime value.
Why confuse sql server by taking 1000 values, pick the max and delete all records below that value?
It's pure math and no magic...

E 12°55'05.63"
N 56°04'39.26"

abdou_kadri
Starting Member

13 Posts

Posted - 2009-05-26 : 13:49:32

sorry Guys Just got it now

Thanks ALL for replies and suggestions

Cheers!!!!!!!!

quote:
Originally posted by Peso

quote:
Originally posted by abdou_kadri

Thanks Peso for your valuable suggestion, How can we limit it for only to search in the top 1000 and not the full table
It makes no sense.
The max of the "last" / "nearest" 1000 values (ordered descending) below a certain datetime value, will ALWAYS be the nearest lower value of that datetime value.
Why confuse sql server by taking 1000 values, pick the max and delete all records below that value?
It's pure math and no magic...

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources