TheSQLGuru (6/17/2013)

---

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.

1) You seem to be scanning the person table. As Chris suggests, this can almost certainly be addressed with proper index.

2) What is the worker table doing? Not sure why that is necessary.

3) We REALLY need the actual query plan!!

4) You probably need OPTION (RECOMPILE) on this statement (even with hard-coded values, but ESPECIALLY if you use variables in your "real" code) due to the extreme data-value-distribution issues you have with the 3 values of comptype. You ABSOLUTELY DO NOT WANT the same plan for each of those 3 values, ESPECIALLY 1 and 3!!