I have attached the execution plans as required.

Waiting for your comments...

Yes, I used Andy's suggestion and it was working fine. After that, Optimizer was suggesting some index. Instead of creating index, I put all the query inside another query.

...

I tried one more thing in above suggestions, as pasted below.

It is not asking for missing indexes and performing much better.

...

I want to do this because when I check performance of a query and I need to run it again and again, I can't clean everything, because I'm not the...

this is the actual plan from prod server.

Still not helping.

We are using a filter "where comp.comp_type = 1" in the query.

If I change the filter comp.comp_type = 2 then it is taking only 15 seconds.

We...

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...

Thanks. I will check.

Use OUTPUT INSERTED.a into @foo2 If you want to get the inserted value.

Use OUTPUT DELETED.a into @foo2 If you want to get the value replaced by update statement.

Add index suggested in Paul's reply, to improve performance of your query.

Gail,

Just ignore nolock in any of my posts (client policy).

I still need an answer.

So should I say that it is the old way of INNER JOINing tables, that will not be supported in future versions !!

Also, when we write "x IN (2,4,6)", optimizer...

Gail, Chris

Thanks a ton.

I will go with queru 1, even if there is no performance gain.

...

Though I know the drawbacks of NOLOCK, it can't be avoided in our case. So, excuse me for that.

And regarding this post, I know that logically all 3 queries are...

Query A

SELECT

e.es_id,

e.site_id,

e.bu_id,

e.comp_code,

e.submit_time,

e.status,

e.currency,

j.lt_id

FROM

dbo.exp_sheet e(nolock)

...