- Home /

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 Few 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.

Comment

Copyright 2019 Redgate Software.
Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges