Execution Plan

Question

Execution Plan

Gopi S

Ten Centuries

Points: 1091
More actions
March 4, 2011 at 4:08 am

#233609

I have a table and have to delete some records based on some logic. First i am taking backup of deleting records. (Ref code: -- Backup the Delete records before deleting). After then either i can use the query under -- Query Option 1 or -- Query Option 2 to finally delete the records. By seeing the execution plan i couldn't decide which one to choose. Pls suggest the best query and pls justify. Thanks in advance
Note: Pls follow the table and query schema as such.
--drop table MainTable
--drop table Lookup_tbl
create table MainTable(ID varchar(10), Date1 varchar(10),Date2 varchar(10),Date3 varchar(10), descp varchar(10))
create table Lookup_tbl(descp varchar(10), flag varchar(10))
CREATE NONCLUSTERED INDEX [idx_MainTable_ID] ON [dbo].[MainTable]
(
[ID] ASC
) ON [PRIMARY]
GO
--drop table DeleteTable
go
DECLARE @DeleteDate DATETIME2
-- SET @DeleteDate = DATEADD(yyyy, -7, GETDATE())
SET @DeleteDate = DATEADD(yyyy, -7, GETDATE()-45)
-- Backup the Delete records before deleting
SELECT LD.* INTO DeleteTable
FROM MainTable LD
INNER JOIN Lookup_tbl D ON LTRIM(RTRIM(LD.descp)) = LTRIM(RTRIM(D.descp))
WHERE CAST(
COALESCE(NULLIF(LD.Date1,''),
NULLIF(LD.Date2,''),
NULLIF(LD.Date3,'')) AS DATETIME2) < @DeleteDate
AND LTRIM(RTRIM(D.flag)) = 'NO'
-- Query Option 1
DELETE LD FROM MainTable LD
INNER JOIN Lookup_tbl D ON LTRIM(RTRIM(LD.descp)) = LTRIM(RTRIM(D.descp))
WHERE CAST(
COALESCE(NULLIF(LD.Date1,''),
NULLIF(LD.Date2,''),
NULLIF(LD.Date3,'')) AS DATETIME2) < @DeleteDate
AND LTRIM(RTRIM(D.flag)) = 'NO'
-- Query Option 2
DELETE LD FROM MainTable LD INNER JOIN
DeleteTable ND ON LD.ID = ND.ID

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

burtsev SSC-Addicted Points: 400 More actions · Answer 1

Hi,

1. Both methods are not what you really need. You need to review OUTPUT clause of DELETE statement. It allows you to backup and delete rows with single statement

DELETE MainTable

OUTPUT DELETED.* into DeleteTable

FROM MainTable LD

INNER JOIN ...

....

2. Where do you think is your bottleneck? Why you care only about DELETE performance and create so awful data scheme and SELECT statement?

Anton Burtsev

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Gopinath,

I'm hoping you've simplified your example, but in this case:

CAST(

COALESCE(NULLIF(LD.Date1,''),

NULLIF(LD.Date2,''),

NULLIF(LD.Date3,'')) AS DATETIME2) < @DeleteDate

will not allow for any index usage, you've created a non-SARGable expression. You will be forced to scan at all times against this table.

Is this schema set in stone or are you still developing the structure? Do you have the option of creating persisted computed columns?

Either way, perhaps you can give us more detail, including DDL, indexes, and a bit of sample data, and perhaps we can find a cleaner way out of this for you. This will be forever painful.

Also, as mentioned above, you'd be better off using the OUTPUT clause as the method to locate the affected records, so you only have to do a single pass on the physical table.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Gopi S Ten Centuries Points: 1091 More actions · Answer 3

As suggested i can use output clause as one way.

This is a ETL process and the deletion logic in the WHERE clause varies from table to table based on business rule. I think we can use computed column while creating the table.

Thanks

Gopi