T.Ashish (6/17/2013)
Provided test data is from my local, so it will not give any issue. Getting data from prod server is not possible for me.I have tried all three options (Two queries I attached and third suggested by you), there is a marginal difference between them. All are taking between 5 - 5.30 minutes to execute and returning 341 rows.
These are the I/O stats :
Table 'worker'. Scan count 1, logical reads 25439, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'person'. Scan count 341, logical reads 17962891, physical reads 0, read-ahead reads 10466, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'comp'. Scan count 1, logical reads 1065, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'os_contract'. Scan count 1, logical reads 146, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Have you tested with a couple of new indexes?
(32 row(s) affected)
Table 'os_contract'. Scan count 32, logical reads 64, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'comp'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'person'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'worker'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden