| Author |
Topic |
|
gealni
Starting Member
2 Posts |
 Posted - 2011-08-31 : 10:13:03
|
| This example shows the Transact-SQL extension used to delete records from a base table that is based on a join or correlated subquery. The first DELETE shows the SQL-92-compatible subquery solution, and the second DELETE shows the Transact-SQL extension. Both queries remove rows from the titleauthors table based on the titles stored in the titles table./* SQL-92-Standard subquery */USE pubsDELETE FROM titleauthorWHERE title_id IN (SELECT title_id FROM titles WHERE title LIKE '%computers%')/* Transact-SQL extension */USE pubsDELETE titleauthorFROM titleauthor INNER JOIN titles ON titleauthor.title_id = titles.title_idWHERE titles.title LIKE '%computers%'I have a large 'titleauthor' table and a smaller (10%) 'titles' table.Which DELETE query is the better one regarding performance (real time) ?I'm Using MS SQL Server 2008 R2 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-31 : 10:36:47
|
| depends on lots of factors like presence of index, amount of data etc. i think JOIN will perform better than IN generally------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-31 : 11:09:30
|
| LIKE '%computers%' will be very inefficient, relative to the actual delete |
|
|
|
gealni
Starting Member
2 Posts |
Posted - 2011-09-01 : 05:53:47
|
quote:
Originally posted by visakh16
depends on lots of factors like presence of index, amount of data etc. i think JOIN will perform better than IN generally
My main-table where the rows are to be deleted has 81.907.221 rows, and the utlity-table that contains the delete-keys has about 100.000 rows. Both tables has index on the key colums.quote:
Originally posted by Kristen
LIKE '%computers%' will be very inefficient, relative to the actual delete
Agree, it was only an simple example - not my actual query. But if we look besides that LIKE is inefficient (and a bad example in this context) - what would be better to use, when deleting rows based on a sub-table ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-01 : 07:29:28
|
| if its a large table and it has index on key columns i think join should have upper hand------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-01 : 08:15:12
|
| "it was only an simple example - not my actual query."Would be better to post the actual query then, otherwise we are all guessing I'm afraid ... |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2011-09-01 : 14:13:19
|
| As Kristen said, Like will make it slower no matter you have index or not. Don't you have type column which identifies whether it is computer or something else. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-09-01 : 15:48:29
|
| I'm thinking changing from the IN or JOIN to an EXISTS might be the better option here. Of course, you would need to evaluate the execution plans and test each one to determine which is better.Also, depending on how many rows are going to be deleted - you might consider batching the deletes instead of performing in one transaction. In many cases, that will perform much better than a single transaction - and also will help manage the transaction log size.Jeff |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-01 : 23:59:41
|
| Thats a good point Jeff------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Which Delete query is better....