what's wrong with this? It's taking so very long? THere is only 450,000 rows. I did the same for PIF900 which has 500,000 rows and it only took 30 seconds. --CREATE CLUSTERED INDEX sourcePIF156_idx ON dbo.pif156_report(loanno) select * from pif156_report where loanno not in ( select loanno from pif156_report where actual_cus_doc_ordered = ' ' and dcorder = ' ' and actual_release_entered = ' ' and denter = ' ' and actual_sent_to_record = ' ' and drecord = ' ' and actual_confirm = ' ' and dconfirm = ' ' -- 11,787 )
Are there any indexes on the fields that you refer to in the subquery? Perhaps that's also the difference between your two tables.
Did you see execution plan? May be there are other index or better one to implementate. Luis Martin Moderator SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important Bertrand Russell All postings are provided “AS IS†with no warranties for accuracy.
I think you have not done the indexing. Use the index Tunning Wizard to create new index quote:Originally posted by v1rtu0s1ty what's wrong with this? It's taking so very long? THere is only 450,000 rows. I did the same for PIF900 which has 500,000 rows and it only took 30 seconds. --CREATE CLUSTERED INDEX sourcePIF156_idx ON dbo.pif156_report(loanno) select * from pif156_report where loanno not in ( select loanno from pif156_report where actual_cus_doc_ordered = ' ' and dcorder = ' ' and actual_release_entered = ' ' and denter = ' ' and actual_sent_to_record = ' ' and drecord = ' ' and actual_confirm = ' ' and dconfirm = ' ' -- 11,787 )
