SQL language use 'Desc'

Question

I test my SQL language find one question, When I delete 'desc' the SQL running so fast but when I add 'desc', the SQL running so slowly. How could I optimize my SQL language. Thank you very much.

DELETE FROM [WHRW_BATCH] 
WHERE [Start_TM] NOT IN (SELECT TOP 10000 [Start_TM] 
                         FROM [WHRW_BATCH] 
                         ORDER BY [Start_TM] DESC

question from:https://stackoverflow.com/questions/65881985/sql-language-use-desc

Answer 1

This looks like SQL Server syntax. In that database, I would suggest an updatable CTE:

WITH todelete AS (
      SELECT wb.*,
             ROW_NUMBER() OVER (ORDER BY Start_TM DESC) as seqnum 
      FROM WHRW_BATCH wb
     )
DELETE FROM todelete
    WHERE seqnum > 10000;

This will keep the 10,000 most recent records.

If the table is quite large (i.e. most records are being deleted), then you might find a temporary table is faster:

select top (10000) wb.*
into temp_whrw_batch
from WHRW_BATCH wb
order by Start_TM desc;

Then remove all rows from the table and re-insert:

truncate table WHRW_BATCH;

insert into WHRW_BATCH
    select *
    from temp_whrw_batch;