Complex SQL with slow performance

Question

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'))