CODEWITHSUNDEEP
sqlsql-server
Sean Anderson
Sean Anderson

Reputation: 29331

Deleting rows via SQL query -- any way to speed up this query?

DELETE from Users 
Where Id in (
    SELECT TOP 200 u.id
    FROM users u JOIN
         playlists pl
         ON u.id = pl.UserId LEFT OUTER JOIN
         playlistitems pli
         ON pli.PlaylistId = pl.id
    GROUP BY u.id
    HAVING count(pl.id) = 1 AND
           count(pli.id) = 0
)

Are there any pitfalls in this SQL query which would cause it to run really slowly? It takes me 1m32s to delete 200 rows, but there's 260,000 that I need to clean up..

Upvotes: 0

Views: 71

Answers (3)

Metaphor
Metaphor

Reputation: 6415

Index each of the tables on the ID column. Preferably a clustered index, but not essential.

given that you already have indexes, try something like this:

;with cte as 
(
    select 
        u.id,
        rank() over (partition by u.id) as rk
    from users u
    join playlists pl on pl.UserId = u.ID
    join playlistites pli on pli.PlaylistID = pl.ID
)
delete from cte
where rk > 200

Upvotes: 1

Paul
Paul

Reputation: 1066

-- insert users to delete in #temp table
SELECT u.id
INTO #toDelete
FROM users u JOIN
     playlists pl
     ON u.id = pl.UserId LEFT OUTER JOIN
     playlistitems pli
     ON pli.PlaylistId = pl.id
GROUP BY u.id
HAVING count(pl.id) = 1 AND
       count(pli.id) = 0


-- increment or decrement at will
set @rowcount 200
select 'dummy' -- force @@rowcount > 0
while @@rowcount > 0
begin

   delete 
   from Users 
   where exists (select * from #toDelete td where td.id = users.id)

end
set @rowcount 0

Upvotes: 0

fonkap
fonkap

Reputation: 2509

I think it is pointless to join from users why don't you do this instead:

DELETE from Users 
Where Id in (
    SELECT TOP 200 pl.UserId
    FROM playlists pl
         LEFT OUTER JOIN
         playlistitems pli
         ON pli.PlaylistId = pl.id
    GROUP BY pl.UserId
    HAVING count(pl.id) = 1 AND
           count(pli.id) = 0
)

Upvotes: 1

Related Questions