How to enhance query to make it run fast and less time?

Question

I work on SQL server 2012 query I face issue : when run query return 10 rows for only one part

it take 50 second I try to run it in another time may be PC have more load

but it take same time 50 second to return 10 rows for only one part .

50 second for 10 rows it is more

I have 10000 part and records related 15 million so I need to enhance performance to be best

so what I do to make query run in small time

this is my query :

see execution plan below :

https://www.brentozar.com/pastetheplan/?id=HyclwfSev

I try to solve issue by doing :

CREATE NONCLUSTERED INDEX index1 
ON [dbo].[Excel_DK] ([PartNumber]) INCLUDE ([ZPartId],[FeatureName],[Value])

but it take 25 second to return 10 rows it still more

are there are any thing can do to enhance performance

Answer 1

From your execution plan it seems that the data in your temporary table is a about ~ 400 000 rows. Lately, because of filtering (using INNER JOIN and WHERE clause) the data flow is reduced to 10 rows.

The temporary table itself, seems to be quite big - the Estimate data size is 1 MB, but the actual is 28 MB.

The above can be easily seen on the next screenshot (check the rows after each block):

So, what you can do is to create smaller version your temporary table and apply the filtering in advance. For example:

SELECT FM.Z_FeatureID, FM.StatusId,FM.SplitFlag,FM.DkFeatureId,FM.separator,FM.separatororder
INTO #TEMP
from #getDeliveryConfiguration FM with(nolock)  
join [Excel_DK].dbo.Excel_DK DUFP with(nolock) on DUFP.FeatureName =FM.DK_Feature 
join [Z2DataCompanyManagement].[CompanyManagers].[Company] C with(nolock) on p.CompanyID = C.CompanyID
where C.CompanyName=@companyName And DUFP.PartNumber=@partNumber

and then replace the #TEMP in your original query. Of course, you can try another filtering, but the idea is to limit the rows in advance and than to read the data from the other tables.