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.