July 3, 2008 at 1:00 am
I would like to delete records for the given day from the table having records for last one year. The chances of having records is minimal. It will have records only if we have to processes for more given day more than once.
My question is should we check for BETTER PERFORMANCE
EXISTS (SELECT * FROM tablename where day = @day)
OR we can directly USE DELETE COMMAND.
My understanding is that when the chances of having records is minimal than we should check for existence and should execute DELETE statement only if it exists will give better performance in this case.
Performance is the ket here considering millions fo records in this table.
Am I correct?
July 3, 2008 at 1:21 am
First a question, do you have an index for the day column? If you do, then my suggestion would be to go with the direct delete.
Also, is the delete done in a single transaction? If so, how and what do you access in the above table?
Regards,
Andras
July 3, 2008 at 3:11 am
Thanks Andras for your response.
YEs I do have Clustered index on the day column and It is in single transaction.
I have many more columns in this table but I used only Day column for this particular process.
July 3, 2008 at 10:56 am
The DELETE will only really take any additional performance hit if it has to wait for a resource lock. In you case, it may be that it has to wait for a page lock. I hope you don't have so little data that you end up with an escalation to to a table lock.
As far as performance, checking will not help you out. The DELETE will only act on records that it needs to act on, so if you have an index on the column in your WHERE clause, the DELETE will know right away that there is nothing to delete. The extra SELECT it probably more overhead than it will be able to save you.
July 3, 2008 at 12:17 pm
Adding the Exists command seems to me to be unnecessary in any case. Will it actually resolve the lack of such rows more efficiently than the Delete command will do by itself?
I don't think the presence/absence of indexes will make a real difference in that. Has someone done tests that show differently?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 3, 2008 at 12:35 pm
GSquared (7/3/2008)
Adding the Exists command seems to me to be unnecessary in any case. Will it actually resolve the lack of such rows more efficiently than the Delete command will do by itself?I don't think the presence/absence of indexes will make a real difference in that. Has someone done tests that show differently?
A logical exercise. A 1,000,000 row table name dbo.MyTable, no indexes. One column, DeleteOnThis, is the column to be used for selecting records to be deleted. Assume at least 10,000 unique values in this column.
DELETE FROM dbo.MyTable
WHERE
DeleteOnThis = @SomeValue;
What happens behind the scenes? SQL Server has to scan the entire table to determine if there are any rows that satisfy the WHERE clause.
IF EXISTS(SELECT 1 FROM dbo.MyTable WHERE DeleteOnThis = @SomeValue)
DELETE FROM dbo.MyTable
WHERE
DeleteOnThis = @SomeValue;
What happens here? The SELECT in the EXISTS has to scan the table until it finds a match the WHERE clause at which point it can stop and return TRUE to the IF, then the DELETE has to scan the entire table to identify and delete all appropriate records. Have you saved anything, may or maybe not. In this case it would depend on when the EXISTS finds the first match.
This is a case where proper indexing helps significantly, and could make the IF EXISTS unnecessary (if it really is).
Any one else want to expand on the logical exercise?
😎
July 3, 2008 at 12:49 pm
The OP mentioned that the DAY column is the clustered index key. Assuming that's the case - there's not going to be a table scan, but a clustered index SEEK. In that case, I can't imagine what good the IF EXISTS syntax will have (you'll get one seek if there's no match, and 2 if there IS).
Come to think of it - I can't think of a circumstance where the EXISTS would be beneficial, since you'd have to repeat the operation if you find a match. Even without indexes, the "straight" delete would force one table scan, the exists would force one OR two table scans (even if the first one stops as soon as it find any match, it's still a partial scan). One is better than "one or somewhat more than one" in my book, no?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 3, 2008 at 1:43 pm
Yep! That's why I tried the logical exercise. Pretty much helps put it in perspective though.
😎
July 3, 2008 at 1:53 pm
Lynn Pettis (7/3/2008)
Yep! That's why I tried the logical exercise. Pretty much helps put it in perspective though.😎
It's late on a thursday before a holiday, so I had to test it out too!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 4, 2008 at 3:03 am
If you are just looking at elapsed time, then doing just a DELETE will be faster. Also, if the DELETE will almost always find data to delete then this is the best approach.
If most times the DELETE will find nothing and you are concerned about the impact of locking, check for existance first. Because you are concerned abuot locking, add a NOLOCK clause to the existance check:
EXISTS (SELECT * FROM tablename WITH (NOLOCK) where day = @day)
The DELETE will put a IX lock on each page it accesses, which could impact other users. If the DELETE finds nothing, that is a lot of locks for no benefit. Doing the EXISTS ... WITH (NOLOCK) has the lightest impact on the database.
IMHO, doing an EXISTS check is only worthwhile on a very heavily used database (many 100s of simultaneous users). If you are currently struggling with too much locking, then an EXISTS is a tactic to consider. If you hardly ever see locking problems, then just do a DELETE.
PS This is another way of saying 'it depends'.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 7, 2008 at 3:28 pm
first
EXISTS (SELECT * FROM tablename WITH (NOLOCK) where day = @day)
this just allows for dirty reads as a select statement won't interfere with other select statements.
second
here's the extremes of what EXISTS (SELECT * FROM tablename WITH (NOLOCK) where day = @day) will do on the given tables where @day = 9/9/1999
Best Case:
id, day
1, 9/9/1999
2, 8/8/1988
3, 8/8/1988
....
100000, 9/9/1999
with this table, the exists returns true right away since the first returned row is 9/9/1999. Then you're delete statement runs
Worst Case:
id, day
1, 8/8/1988
2, 8/8/1988
3, 8/8/1988
....
100000, 9/9/1999
With this table, the exists scans/seeks the entire table and returns true once it finds the record.
now Delete tablename where day = '9/9/1999'
on both tables the delete has to scan/seek the entire table and find the records.
So, When there are no records, you scan/seek the entire table
When you have a record, you scan/seek the entire table for the first record, then you against scan/seek the entire table to delete ALL of the records.
seems like break even/lose both ways.
With all things said and done, if you have no rows where day = '9/9/1999', both the delete and exists statement should run the same speed; while if there are records, you have the overhead of first scanning for your first record, then repeating but this time for all of the records.
July 8, 2008 at 12:02 am
Thanks all.
I got my answerd from valued members in this forum.
NOT TO USE (IF EXISTS)
July 9, 2008 at 10:53 am
Hi,
The best thing is use DELETE command directly.
Thanks -- Vj
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply