Posted by Erland Sommarskog on 06/25/05 12:07

(kjaggi@hotmail.com) writes:
> -- query --
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
>
> This query runs quite fast with only the data above but when both
> tables get million plus rows, the query simply bogs down. Is there a
> better way to write this query?

Beside the obvious point from Razvan about indexes, if you are on a multi-
CPU box, you can try this at the end of the query:

OPTION (MAXDOP 1)

this turns off parallelism. I've seen SQL Server use massive parallel
plans for this type of query, when a non-parallel plan have been much
faster.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

• Next in forum: Re: BINARY_CHECKSUM algorithm
• Prev in forum: Re: Alternate to a not in query
• Thread view: Re: Alternate to a not in query

[Reply to this message]