Terrible performance. A lot of locks.

F. 2016-07-13 08:46:46

Hi,

the following statement is used by a client application. I figure that the bottleneck is that the OID & AID (PK) cannot be fast enough read and created.

Any inside is greatly appreciated

SELECT AV.AID, AV.OID FROM ANTRAGVARIABLE AV WHERE AV.AID = @P1 AND AV.AOID = @P2 AND AV.AVORGANGOID = (SELECT AVO.OID FROM ANTRAGVORGANG AVO WHERE AVO.AID = AV.AID AND AVO.AOID = AV.AOID AND AVO.VORGANG = (SELECT MAX(AVO2.VORGANG) FROM ANTRAGVORGANG AVO2 WHERE AVO2.AID = AVO.AID AND AVO2.AOID = AVO.AOID AND AVO2.VORGANG <= @P3 AND EXISTS (SELECT AV2.OID FROM ANTRAGVARIABLE AV2 WHERE AV2.AID = AVO2.AID AND AV2.AOID = AVO2.AOID AND AV2.AVORGANGOID = AVO2.OID AND AV2.VOID = AV.VOID AND AV2.MOID = AV.MOID AND AV2.K_TARIFSPARTE = AV.K_TARIFSPARTE AND AV2.TARIFOID = AV.TARIFOID ))) AND EXISTS (SELECT PV.OID FROM ANTRAGVERSION AVE, PRODUKTVARIABLE PV WHERE AVE.AID = AV.AID AND AVE.OID = @P4 AND PV.AID = AVE.AID AND PV.VOID = AV.VOID AND PV.K_TARIFSPARTE = AV.K_TARIFSPARTE AND (PV.TARIFOID = @P5 OR PV.TARIFOID = AV.TARIFOID) AND PV.B_VMBEZUG = AV.B_VMBEZUG AND PV.PROID = AVE.PROID AND PV.PRVERSION = AVE.PRVERSION) ORDER BY SORTIERUNG ASC, AID ASC, OID ASC BR F.

Sujai Karnam 2016-07-19 10:10:05
Share the execution plan
F. 2016-07-22 12:20:21
Hi,

sorry for the delay. It looks like that changing the MAXDOP and "Cost Threshold for Parallelism" took care of this problem. Thank you very much for your effort! BR F.

F. 2016-07-22 09:55:45
Hi,

sorry for the delay. It looks like that changing the MAXDOP and "Cost Threshold for Parallelism" took care of this problem. Thank you for your effort!
BR F.