Hi, We are running one query which takes one hour to finish. How can i make it faster? I have attached the execution plan below. Any idea? Hash match take 26% from overall plan. I could not attached full execution plan as the plan is very length and i did not allowed to upload .sqlplan files here.i have attached the part for hash match.
SELECT DISTINCT CustAbbr ,LeadCount + Package Package ,Device ,SUM(Amount) AS Amount ,Process ,Field001 AS Site ,'2016' AS Year ,'11' AS Month FROM ( SELECT DISTINCT SUBSTRING(h.invoiceno, 1, 2) AS Prefix ,h.CustAbbr ,h.Device ,s.LEADCOUNT ,s.Package ,SUM(h.TotalAmount) AS Amount ,Process AS ProcessID ,( CASE WHEN ( TempColForSummary LIKE '%ASSEMBLY%' OR ProcessType LIKE 'AAS%' OR ProcessType LIKE 'ADDER%' ) THEN 'ASY' ELSE ( CASE WHEN TempColForSummary LIKE '%TURNKEY%' THEN ( CASE WHEN ( Process LIKE 'ASSEMBLY%' OR Process LIKE '%GRIND%' OR Process LIKE '%WAFER PROBE%' ) THEN 'ASY' ELSE 'TST' END ) ELSE 'TST' END ) END ) AS Process ,MAX(ndflag) NDFlag ,MAX(bondtype) Bondtype ,Field001 FROM [S-SQL-ERPRPTDB].[carsemerp].dbo.[SA_INVASY&TST_RPT] h(NOLOCK) LEFT JOIN [S-SQL-ERPRPTDB].[carsemerp].dbo.[SSR_FG_Integration] s(NOLOCK) ON h.MESID = s.LotID AND ( Command LIKE 'TST03%' OR Command LIKE 'TST2.5%' ) AND SysID = ( SELECT MAX(sysid) FROM [S-SQL-ERPRPTDB].[carsemerp].dbo.[SSR_FG_Integration] a(NOLOCK) WHERE a.LotID = s.LotID AND a.Command = s.Command ) INNER JOIN [S-SQL-ERPRPTDB].[carsemerp].dbo.[DC_BOMHeader] d(NOLOCK) ON d.BOMID = s.PartID INNER JOIN [S-SQL-ERPRPTDB].[carsemerp].dbo.[SA_ASP_Trend] a(NOLOCK) ON a.Device = h.device AND a.CustAbbr = h.CustAbbr AND a.LEADCOUNT = s.LeadCount AND a.PACKAGE = s.Package AND NOT EXISTS ( SELECT TOP 1 1 FROM [S-SQL-ERPRPTDB].[carsemerp].dbo.[SA_ASP_Trend] b WHERE a.CustAbbr = b.CustAbbr AND a.LeadCount = b.LeadCount AND a.Package = b.Package AND a.Device = b.Device AND b.Year < '2016' ) AND IndexID = ( SELECT MAX(IndexID) FROM [S-SQL-ERPRPTDB].[carsemerp].dbo.[SA_ASP_Trend] c WHERE c.CustAbbr = a.CustAbbr AND c.Device = a.Device AND c.LeadCount = a.LeadCount AND c.Package = a.Package ) WHERE InvoiceDate BETWEEN '2016-10-27 18:30:00' AND '2016-11-24 18:29:59' AND h.InvoiceNo NOT LIKE '%F' AND h.InvoiceNo NOT LIKE 'PD%' GROUP BY SUBSTRING(h.invoiceno, 1, 2) ,h.CustAbbr ,h.Device ,s.LEADCOUNT ,s.Package ,Process ,TempColForSummary ,ProcessType ,Field001 ) a WHERE a.Bondtype = 'M' AND NDFlag LIKE '2016%' GROUP BY CustAbbr ,Device ,LeadCount ,Package ,Process ,Field001
Full SQL Plan at below URL
SQL Plan Click This To view the plan