Hakan Axheim

Hakan Axheim

  • 1.8k
  • 111
  • 4.9k

Complex SQL with slow performance

Nov 29 2019 1:46 AM
Hi,
 
I have an SQL that is a little bit complex and I think the performance of this SQL is to slow.
We have a large system that retrieves data from the database (SqlServer 2016) in a similar way as this SQL.
Sometimes the performance is good but in some SQL it is too slow. 
Can I use another concept to write this SQL or is this the most optimal SQL statement?
The TABLE_A has about 3 million records. As you see  TABLE_A is used also in the exists statement.
 
Here comes the SQL:
 
SELECT TABLE_A.*
FROM TABLE_A
WHERE EXISTS
(
SELECT 'X'
FROM Authority AS Auth
LEFT OUTER JOIN Units AS Unit ON (Auth.UnitCode <> 0 AND Unit.UnitCode = Auth.UnitCode)
LEFT OUTER JOIN Responsibilty AS Resp ON (Resp.RespId = Auth.RespId)
LEFT OUTER JOIN Performers AS ANO ON (Auth.PerformId <> 0 AND ANO.PerformId = Auth.PerformId)
LEFT OUTER JOIN Code AS Code ON (Resp.RespId = Code.CodeId)
LEFT OUTER JOIN Organisation AS Org ON (Auth.OrgId <> ' ' AND Org.PEGGUID = Auth.OrgId)
WHERE Auth.SSN = '195001010101'
AND Auth.Roll = 'System'
AND '20191125' BETWEEN Auth.FROMDAT AND Auth.TOMDAT
AND (Auth.PEGGUID <> ' ' OR Auth.PerformId <> 0 OR TABLE_A.UNITAREAID = ' ' OR TABLE_A.UNITAREAID LIKE
(CASE WHEN ISNULL(Unit.UnitCode, -1) = -1
THEN Auth.ORGENHNR + '%'
ELSE Unit.ORGENHNR + '%'
END))
AND ((TABLE_A.UnitCode != 0 AND Auth.DBV = ' ') OR(TABLE_A.UnitCode = 0 ))
AND (
(Auth.UnitCode = 0 AND Authority.PEGGUID = ' ' AND Auth.PerformId = 0)
OR TABLE_A.UnitCode = 0
OR Auth.UnitCode = TABLE_A.UnitCode
OR ANO.UnitCode = TABLE_A.UnitCode
OR (ORG.OBJID = TABLE_A.ENHKOD AND ORG.OBJTYPE = 999961300)
)
AND ((Auth.VERKFORM = 0 AND Auth.RespId = 0) OR TABLE_A.BuildingID = 0 OR Resp.BuildingID = TABLE_A.BuildingID)
AND ((Auth.VERKFORM = 0 AND Auth.RespId = 0) OR TABLE_A.RespId = 0 OR Auth.RespId = TABLE_A.RespId)
AND (Auth.PerformId = 0 OR Auth.PerformId = TABLE_A.PerformId)
AND (Auth.REGI = 0 OR TABLE_A.REGI = 0 OR Auth.REGI = TABLE_A.REGI)
)
AND ((TABLE_A.SSN LIKE '198401001111%')
AND TABLE_A.XTRAINFO2 IN('1','2'))
 

Answers (2)