How to rewrite query to take small time when executed ?

[Nop_PartsFamilyAttribute].[_dta_in… index fragmentation?

First of all, remove all those NOLOCK in the query. I assume that you want your queries to return the correct result? If you put in NOLOCK on every table you are basically say "I don't care if the result is correct or not".

Next the execution plan you provided only has estimated values. This makes it more difficult to assess where time is being spent. However, I notice that the plan estimates that the query will return two rows, and you say that it returns 50000. This indicates that there is a problem with the estimates somewhere. Maybe your statistics are out of date?

Hi @ahmed salah ,

According to the exection plan you provided, no enhancement is needed since it is already index seek and seek predicate under it.

It is better for you to provide the execution plan of your complete query which returns 50000 rows so that we could proceed to check any enhancement needed.

In addition, there are some suggetions with your query provided.

1. Manually update statistics.
2. Use trace flag 2371(DBCC TRACEON(2371,-1))
3. Find any possible to change from too many joins to exists.
4. Consider to use some tune tools like Database Engine Tuning Advisor (DTA).

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa