Performance issues

Transact-SQL
1

Would like some performance tuning in the join conditions over here, Any comments to improve the performance is much appreciated

SELECT xxx

FROM MECABSP MEC
INNER JOIN MEACBSP MEA ON ( MEC . ACCIPK = MEA . ACCIPK )
INNER JOIN MEMSRLP MEM ON ( MEC . MSPIPK = MEM . MSPIPK
AND MEC . ACCIPK = MEM . ACCIPK )

LEFT OUTER JOIN MEEYBLP MBL ON MBL . LNKIPK = MEC . MSPIPK
AND MBL . LKFLCD = 150 AND MBL . BLCCOD = 118
AND MBL . BLKEDT >= CURRENT DATE

LEFT OUTER JOIN MEEYABP MEE
ON MEE . LNKIPK = MEC . ACCIPK
AND MEE . LKFLCD = 130
AND MEE . ABTCOD = ( SELECT MAX ( ABTCOD )
FROM MEEYABP MEY
WHERE MEY . LNKIPK = MEC . ACCIPK
AND MEY . LKFLCD = 130 )

LEFT OUTER JOIN MECDDCP MST ON MST . CODVAL = MEM . MSPTYP
AND MST . CODCAT = 'MSPTYP' AND MST . LNGCOD = 1

LEFT OUTER JOIN MECDDCP ACT ON ACT . CODVAL = MEA . ACCTYP
AND ACT . CODCAT = 'ACCTYP' AND ACT . LNGCOD = 1

LEFT OUTER JOIN MECDDCP COD ON COD . CODVAL = MEA . CPYNBR
AND COD . CODCAT = 'CPYNBR' AND COD . LNGCOD = 1

LEFT OUTER JOIN (
SELECT P1 . MSPIPK , P1 . DMMSIK , P2 . MSPTYP
FROM MEDMMSP P1
INNER JOIN MEMSRLP P2 ON ( P1 . MSPIPK = P2 . MSPIPK )
) T10 ON T10 . DMMSIK = MEC . MSPIPK
LEFT OUTER JOIN (
SELECT H1 . MSPIPK , H1 . DMMSIK , H2 . MSPTYP
FROM MEDMMSP H1
INNER JOIN MEMSRLP H2 ON ( H1 . DMMSIK = H2 . MSPIPK )
) T11 ON T11 . MSPIPK = MEC . MSPIPK

LEFT OUTER JOIN (
SELECT CC1 . MSPIPK AS CC_MSPIPK
, BP1 . MSPIPK AS BP_MSPIPK
, BP1 . LKFLCD AS BP_LKFLCD
, BP1 . LNKIPK AS BP_LNKIPK
FROM MEMSRLP CC1
INNER JOIN MEMSRLP BP1 ON (
CC1 . ACCIPK = BP1 . ACCIPK
AND CC1 . LKFLCD = 160
AND BP1 . LKFLCD = 100
AND CC1 . MSPTYP = 6
AND BP1 . MSPTYP = 1
)
) CC11 ON CC11 . CC_MSPIPK = MEC . MSPIPK

LEFT OUTER JOIN (
SELECT NMP . PERIPK , NMP . IPKSEQ
, NMP . LSTNAM , NMP . FRSNAM
, NMP . MDLINL , NMP . NAMMFR
, ROW_NUMBER ( ) OVER ( PARTITION BY NMP . PERIPK
ORDER BY NMP . IPKSEQ DESC ) RN
FROM MEPRNMP NMP
) P ON ( CASE
WHEN BP_MSPIPK IS NOT NULL
AND MEC . LKFLCD = 160 AND BP_LNKIPK = P . PERIPK
THEN 1

	WHEN BP_MSPIPK IS NULL 
	AND MEC . LKFLCD = 100 AND MEC . LNKIPK = P . PERIPK 
	THEN 1 
ELSE 0 END ) = 1 AND P . RN = 1

WHERE MEC . CARNBR = CAST ( IN_CARNBR AS DECIMAL ( 15 , 0 ) )
OR ( MEC . CARNBR IN ( SELECT T4 . CARNBR
FROM MECABSP T1
INNER JOIN MEMSRLP T2 ON ( T1 . ACCIPK = T2 . ACCIPK AND T2 . MSPTYP = 1 )
LEFT JOIN MEDMMSP T3 ON ( T2 . MSPIPK = T3 . MSPIPK )
LEFT JOIN MECABSP T4 ON ( T1 . ACCIPK = T4 . ACCIPK AND
( T4 . MSPIPK = T3 . MSPIPK OR T4 . MSPIPK = T3 . DMMSIK ) )
WHERE T1 . CARNBR = CAST ( IN_CARNBR AS DECIMAL ( 15 , 0 ) )
)
AND ( CASE
WHEN IN_CARSTF = 0 THEN 1
WHEN IN_CARSTF = 1
AND ( MEA . ACCSTS = 0 AND MEA . ACXPDT >= CURRENT DATE )
AND ( MBL . BLCCOD IS NULL AND MEM . MSPSTS = 0 )
AND ( MEC . CARSTS = 0 )
THEN 1

WHEN IN_CARSTF = 2 
AND ( MEA . ACCSTS = 0 AND MEA . ACXPDT >= CURRENT DATE ) 
AND ( MBL . BLCCOD IS NULL AND MEM . MSPSTS = 0 ) 
AND ( MEC . CARSTS = 999 ) 
THEN 1 

WHEN IN_CARSTF = 2 
AND ( MEA . ACCSTS = 0 AND MEA . ACXPDT >= CURRENT DATE ) 
AND ( MEM . MSPSTS = 999 OR MBL . BLCCOD = 118 ) 

THEN 1 
WHEN IN_CARSTF = 2 
AND ( MEA . ACCSTS = 999 OR MEA . ACXPDT < CURRENT DATE )

THEN 1
ELSE 0 END
) = 1
)
WITH UR ;

2

what does the execution plan look like? Do you have any DDL and sample data for us to try?

I see this which will cause some issues

WHERE MEC . CARNBR = CAST ( IN_CARNBR AS DECIMAL ( 15 , 0 ) )

3

This will accept 12 numeric value

4

Hi

Its many many joins in a single SQL

The recommendation for joins is max 4
Per SQL statement

One idea is to break up the SQL joins

Select join 1 select
into temp table 1

Select join 2 temp table 2
Into temp table 3

1 Like
5

Another idea is if you do the filtering stuff
First before joining the performance will improve drastically

I mean 100 000 rows vs 10 rows

Example

Select 1 join select 2

Select where name John join select 2

1 Like
6

No, that's a very odd statement. There is no such "recommendation" from anyone reputable. You need as many joins as the query requires. The key is to make those joins as efficient as possible.

2 Likes
7

My BAD Scott

Wrong Stuff in my memory ( As usual memory not reliable )

Thank you

2 Likes
8

Thank you much, trying it

9

WITH UR ... so this is DB2!?

Another improvement would be to combine multiple lookups against MECDDCP to a single query and use CASE to match each individual value.

For any real details, we need DDL including all index definitions, as mike01 mentioned in the first comment.

10

I think its a try to make 2 queries out of this so you can skip the OR in the WHERE. So first try the first condition and then create a new query with the second one. If both are fast you can combine the 2 queries with union (all) to make 1 resultset.

You may also want to use a CTE, Common Table Expression, so you can first prepare your dataset. This would make it much easier to understand. Also it would be more readable if you place some comments on the numbers, for example:

AND CC1 . LKFLCD = 160 -- Fill in the value of what 160 is so other people can read what you do
AND BP1 . LKFLCD = 100 /* multiline comments */
AND CC1 . MSPTYP = 6 -- 6 = something
AND BP1 . MSPTYP = 1 -- 1 = something else

Good luck with this query, it seems like a lot of logic :wink: