Help needed to tune the long running SQL

SQL Server Administration
1

Hi, We created a Peoplesoft PS query with below SQL.

SELECT I.COL1, N.COL2, L.COL3, J.COL4, J.COL5, N.COL6, N.COL7, N.COL8, L.COL9, M.COL10, N.COL11, M.COL12, K.COL13, M.COL13, L.COL14, P.COL15, J.COL16, P.COL17, P.COL18, P.COL19, L.COL20, L.COL21, CASE WHEN L.COL22 <> ' ' THEN
(SELECT JRNL.COL23
FROM TBLJRNL JRNL WHERE
JRNL.COL3 = L.COL3
AND JRNL.COL22 = L.COL22
AND JRNL.COL24 = L.COL24
AND JRNL.COL20 = L.COL20
)
END, L.COL25, L.COL26, L.COL27, L.COL22, L.COL28, L.COL24, L.COL29, L.COL30, L.COL31, L.COL32, L.COL33, L.COL34, L.COL35, L.COL36, DECIMAL(CASE
WHEN K.COL13 = 0
AND L.COL30 IN ('LINEDESCR1','LINEDESCR2')
THEN CASE WHEN L.COL31 = 'XXX' THEN P.COL37 WHEN L.COL31 = 'YYY' THEN( P.COL37 * -1 ) END
END,10, 3), L.COL38
FROM TABLEJ J, TABLEK K, TABLEL L, TABLEM M, TABLEN N, TABLEP P, TABLEI I
WHERE M.SETID = 'MASTER'
AND ( M.COL2 = 'ENTITY'
OR ' ' LIKE 'ENTITY')
AND M.SETID = N.SETID
AND M.COL2 = N.COL2
AND M.COL39 = N.COL39
AND I.COL40 = J.COL40
AND I.COL40 = '3'
AND ( J.TMS_VIN = '4XXXXXYYYYYY'
OR ' ' LIKE '4XXXXXYYYYYY')
AND J.COL5 = K.COL5
AND K.COL5 = P.COL5
AND K.COL41 = P.COL41
AND K.COL42 = P.COL42
AND K.COL28 = L.COL28
AND K.COL41 = L.COL41
AND K.COL21 = L.COL21
AND K.COL41 = M.COL41
AND L.COL30 IN ('LINEDESCR1','LINEDESCR2')
AND K.COL13 = 0
AND L.COL3 = P.COL3
ORDER BY 2, 4, 13, 14

Two of the records contains millions of rows and above SQL query taking more time (in hours) for its execution. Can I get any suggestions to tune above SQL? I can provide more details if needed.

Thank you
Sukumar.

2

Are you sure the database is SQL Server? DECIMAL(...) isn't a valid TSQL function.

3

Hi Jason.. It's DB2

4

This is a Microsoft SQL Server forum. You'll likely find better advise in a dedicated DB2 forum.

That said, the correlated sub-query isn't doing you any favors (this is likely the biggest killer as it is executing once for every row in the outer query). Also, I'd suggest getting away from the prehistorically old ANSII89 style joins and switch to the more modern ANSII92 & later JOIN ... ON syntax. Beyond that make sure you have usable indexes on on all of your tables to support the search predicates and the joins.