Complex SQL with slow performance

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Roshan Patil6y ago
Concerns in your query
1. You are using lot of tables in you sub queries
2. You are performing wildcard string search and function usage on you dense table TABLE_A.

For improvement you need to split your query.
Good that you have used joins in the sub query, but take output of you subquery (there TABLE_A is not involved) in to a temp table. Later user this temp table to join against the main table (TABLE_A).
Make sure to have an index on TABLE_A.SSN column. Also check for requirement of indexes on other columns involved in the joins or wher claus.
0
Dharmesh Sharma6y ago
You are using sub query which is give you slow response always try to use join or inner join to get data with existing where clause.
0