I read Gail's article and ended up using OPTION (RECOMPILE), It reduces the execution time from 9 seconds to 4 seconds

Can anyone please explain what exactly happened after using OPTION...

I've already added the link in the first post, Updated the execution plan with #tables

Please download the execution plan from here: http://files.vikasrana.com/ExecutionPlan.zip

If required I can upload the database with only...

TempDB Details,

Size : 573mb

Space Available : 505mb

num_of_writes 1560971

avg_write_stall_ms11.49514693098078055944612

num_of_reads 944612

avg_read_stall_ms ...

Please download it form here

Table 'DiaVendor'. Scan count 248284, logical reads 496568, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table...