SQLTeam.com | Weblogs | Forums

Can you help me enhance query performance?

I work on SQL server 2012 it take too much time to enhance it
so how to enhance it to take small time
https://www.brentozar.com/pastetheplan/?id=H1XwcNHXv
how to write query this best enhance to take less time

hi

looking at it it looks too many joins !!!

lets say you have

select1
join
select 2
join
select 3
join
select 4

if select 3 is major .. do it outside

select 1
join
select 2
join
select 4
INTO
Select ABC

select ABC
join
select 3

but i see index remaining on plan so how to solve it

not able to understand you ...

on execution plan there are non cluster index on table part family attribute so what i do to solve that

just wait for some more time .. Scott or Jeff( both Jeffs) will help you

-- my thoughts
do what i told you
dont think about the indexes ..already there
see performance

if performance improvements there

then you can think about other things
-- add new indexes
-- the indexes already there .. r they being used ?
-- sometimes indexes need to be dropped .. if they are against performance
-- or existing indexes need to be modified

cluster the temp table on ( status, PLID ). For best performance, add the clustered index before loading the table

change the nonclus index on the fpl table to be keyed by ( [Key], Value ), rather than by just ( [key] ) alone.

1 Like

The plan is an estimated execution plan,. It would be better if you posted an actual execution plan AND attached it here instead of on Brent's site.

I think the key here is that you're using DISTINCT, which means that you have an accidental cross join going on (at least a wicked accidental many-to-many join). Before you start messing around with indexes (which may not help at all because of the reason you're using DISTINCT), post a real actual execution plan here and also let us know if you REALLY have 3,698,580 million rows in the Nop_PartsFamilyAttribute table.