NewID() taking long time to process

Question

I am executing the code below for a table with 2 million plus records but NewID() is taking a long time to run.

Can you suggest an alternate method to make the query faster?

DELETE FROM Table1
WHERE ID NOT IN (SELECT TOP 60000 ID FROM Table1 
WHERE flag <> '1'
ORDER BY NEWID()) 
AND
ID NOT IN(SELECT ID FROM Table1
WHERE flag = '1');

does your Flag coulum have an index? Provide a create table/ index definitions. — brian chandley, Jan 09 '15 at 18:56
What are you trying to do with this code? With the TOP 60000 and the ORDER BY NEWID() it's just going to randomly select 60000 IDs. Is that your goal? — Mike M., Jan 09 '15 at 18:58
Looks like it is intended to delete all but a randomly selected 60k rows that have flag<>'1'. — Tab Alleman, Jan 09 '15 at 18:59
Yes, flag column has an Index. I am using Newid() to select random 60000 rows from the table where flag <> '1' and rows where flag = '1'. Delete the rest rows. — Sam, Jan 09 '15 at 18:59
Hi "NoDisplayName". I am using Newid() to generate random 60000 rows. — Sam, Jan 09 '15 at 19:16

score 0 · Answer 1 · answered Jan 09 '15 at 19:03

0

How about this... should be twice as fast

DELETE FROM Table1
WHERE ID NOT IN (SELECT TOP 60000 ID FROM Table1 
                 WHERE flag <> '1'
                 ORDER BY NEWID()) 
   AND flag != '1';

answered Jan 09 '15 at 19:03

Hogan

69,564
10
76
117

I started to suggest the same, but if there are duplicate IDs in the table, with different values for flag, the results will not be the same. – Tab Alleman Jan 09 '15 at 19:03

NewID() taking long time to process

1 Answers1