Optimize query contains too many inner join and rows

user3868796

I have the following query

SELECT * 
INTO ##TempStaffT
FROM Staff HF 
WHERE  HF.[businessline_id]='T'
AND HF.[offices_id] IN (SELECT * FROM ##TempParamOffice)
AND HF.[specialism_id]IN (SELECT * FROM ##TempParamSpecialism)
AND HF.[onetouch]IN (SELECT * FROM ##TempParamConsultant)

 SET @sql4 = N'

INSERT INTO ##TempFees(' + @columns1 + ',CALF_AN ,BusinessName)
SELECT distinct ' + @columns2 + ',p.CALF_AN ,CB.[TempBusinessName] as BusinessName
   FROM 
     dbo.WTEFAC EF 
  inner JOIN  dbo.WTFAC F ON EF.EFAC_NUM = F.EFAC_NUM 
  inner JOIN  dbo.WTFACINFO BS ON F.FAC_NUM = BS.FAC_NUM 
  inner JOIN dbo.WTLFAC LF ON F.FAC_NUM = LF.FAC_NUM 
  inner JOIN dbo.WTRUBVARIANTEFAC WRU ON LF.RINT_ID = WRU.RINT_ID 
  inner JOIN dbo.WTACUMFAC WTA ON WRU.RUV_ID = WTA.RUV_ID 
  inner JOIN ##CUM_CODEHT WTA1 ON WTA.CUM_ID = WTA1.CUM_ID 
  inner JOIN dbo.WTVTAT TAT ON BS.TIE_ID = TAT.TIE_ID AND BS.RFAN_ID = TAT.RFAN_ID AND BS.PER_ID = TAT.PER_ID AND BS.CNT_ID = TAT.CNT_ID
  inner JOIN dbo.PYCONTRAT CC ON TAT.PER_ID = CC.PER_ID AND TAT.CNT_ID = CC.CNT_ID 
  inner JOIN dbo.WTMISS M ON CC.PER_ID = M.PER_ID AND CC.CNT_ID = M.CNT_ID 
  inner JOIN dbo.WTCNTI COT1 ON M.PER_ID = COT1.PER_ID AND M.CNT_ID = COT1.CNT_ID
  inner JOIN dbo.WTQUAEU Q ON COT1.TIE_ID = Q.TIE_ID AND COT1.QEU_CDE = Q.QEU_CDE
  inner JOIN dbo.WTSCCT C ON CC.RGPCNT_ID = C.RGPCNT_ID AND CC.PER_ID = C.PER_ID AND CC.CNT_ID = C.CNT_ID --AND''SECT3'' = C.STTQ_COD 
  INNER JOIN ##TempStaffT HF ON C.VAPO_CODE = HF.onetouch  COLLATE Latin1_General_CI_AS
  inner JOIN 
##TempA AS p  ON p.CNT_ID = COT1.CNT_ID  inner JOIN 
 ##TempB AS p1  ON p1.CNT_ID = COT1.CNT_ID
  CROSS JOIN [dbo].[CustBusinessTable] CB
WHERE  CB.[TempBusinessName]=''Pure Temp Fees''

GROUP BY p.CALF_AN,CB.[TempBusinessName]
'
;
PRINT @sql4;

My problem is that the query above took 20 min to be executed because ##TempStaffT contains too many rows , how can I optimize it ? I use many temp tables but it seems not to be worked , Any idea ? Many thanks in advance .

Albin Sunnanbo

Create an index on ##TempStaffT

CREATE INDEX IDX_TempStaffT_onetouch ON ##TempStaffT(onetouch)

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

SQL Server Query optimization : too many self inner join

From Dev

Redshift Query returning too many rows in aggregate join

From Dev

How to optimize query with two inner join

From Dev

How to use inner join in given query and optimize it?

From Dev

Slow query inner join many tables HSQLDB

From Dev

mysql join query taking too many resources

From Dev

SQLITE3 Inner Join Returning Too Many Tuples

From Dev

Too many inner classes?

From Dev

Optimize query with inner queries

From Dev

How to optimize a MySQL SELECT query with a LEFT JOIN in main query and an INNER JOIN in subquery?

From Dev

How to optimize a MySQL SELECT query with a LEFT JOIN in main query and an INNER JOIN in subquery?

From Dev

SELECT, JOIN, and a WHERE != statement returning too many rows

From Dev

Optimize query expression with .contains()

From Dev

Optimize too many if else condition

From Dev

Query involving multiple tables: INNER JOIN excludes some rows

From Dev

mysql innodb multiple index locks too many rows during query

From Dev

google big query limit clause returning too many rows

From Dev

System.LimitException: Too many query rows: 50001 error in trigger

From Dev

Yii: optimize LEFT OUTER JOIN to INNER JOIN

From Dev

INNER JOIN results in no rows

From Dev

Inner join rows not affected

From Dev

INNER JOIN results in no rows

From Dev

Enumerating rows in a inner join

From Dev

MySQL Inner Join with No Rows

From Dev

How to optimize the inner query mysql

From Dev

INNER JOIN Query is not working

From Dev

The Difference Inner Join Query

From Dev

query with INNER JOIN

From Dev

Mysql query with inner join