By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,018 Members | 930 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,018 IT Pros & Developers. It's quick & easy.

How to optimize this query in DB2

P: n/a
Greetings all,
We have a complicated statement in DB2 which takes long hour to
complete and we have created most of the indexes. Does anybody knows
how to tune the following statement to optimize the query?

"SELECT AL3.LAB, AL3.DEPARTMENT, AL6.NAME, Count (AL4.ITEM),
AL5.SALES_TERRIOTARY, AL1.DATE_SERVICE
FROM YR2005.REQUESTX AL1, YR2005.RESULTP AL2, YR2005.PANEL AL3,
YR2005.RESULTV AL4, YR2005.DOCTOR AL5, YR2005.DEPT AL6
WHERE (AL2.REQUEST=AL1.RELREC AND AL3.CODE=AL2.PANEL AND
AL3.LAB=AL1.LAB AND AL4.RESULTP=AL2.RELREC AND AL1.DOCTOR=AL5.CODE
AND AL3.DEPARTMENT=AL6.CODE AND AL3.LAB=AL6.LAB) AND ((AL1.LAB LIKE
'%KLG%' OR AL1.LAB LIKE '%OPD%') AND (NOT AL4.ALPHA='/')
AND (AL1.DOCTOR LIKE '%10%' OR AL1.DOCTOR LIKE '%11%' OR AL1.DOCTOR
LIKE '%12%' OR AL1.DOCTOR LIKE '%13%' OR AL1.DOCTOR
LIKE '%14%' OR AL1.DOCTOR LIKE '%15%' OR AL1.DOCTOR LIKE '%16%' OR
AL1.DOCTOR LIKE '%18%' OR AL1.DOCTOR LIKE '%1A%' OR AL1.DOCTOR
LIKE '%21%' OR AL1.DOCTOR LIKE '%23%' OR AL1.DOCTOR LIKE '%24%' OR
AL1.DOCTOR LIKE '%25%' OR AL1.DOCTOR LIKE '%26%' OR AL1.DOCTOR
LIKE '%27%' OR AL1.DOCTOR LIKE '%28%' OR AL1.DOCTOR LIKE '%2A%' OR
AL1.DOCTOR LIKE '%3%' OR AL1.DOCTOR LIKE '%3A%' OR AL1.DOCTOR
LIKE '%3B%' OR AL1.DOCTOR LIKE '%4B%' OR AL1.DOCTOR LIKE '%9%' OR
AL1.DOCTOR LIKE '%ANC%' OR AL1.DOCTOR LIKE '%CCU%' OR AL1.DOCTOR
LIKE '%GICU%' OR AL1.DOCTOR LIKE '%GOT%' OR AL1.DOCTOR LIKE '%HDW%' OR
AL1.DOCTOR LIKE '%ICU%' OR AL1.DOCTOR LIKE '%K4%' OR AL1.DOCTOR LIKE
'%K7%' OR AL1.DOCTOR LIKE '%KK1%' OR AL1.DOCTOR LIKE '%KK2%' OR
AL1.DOCTOR LIKE '%KK3%' OR AL1.DOCTOR LIKE '%KK5%' OR AL1.DOCTOR LIKE
'%KK6%' OR AL1.DOCTOR LIKE '%KK7%' OR AL1.DOCTOR LIKE '%KK8%' OR
AL1.DOCTOR LIKE '%KK9%' OR AL1.DOCTOR LIKE '%KPKK %' OR AL1.DOCTOR LIKE
'%LR%' OR AL1.DOCTOR LIKE '%MH%' OR AL1.DOCTOR LIKE '%MPC%' OR
AL1.DOCTOR LIKE '%P1%' OR AL1.DOCTOR LIKE '%P10%' OR AL1.DOCTOR LIKE
'%P2%' OR AL1.DOCTOR LIKE '%P8%' OR AL1.DOCTOR LIKE '%P9%' OR
AL1.DOCTOR LIKE '%PC%' OR AL1.DOCTOR LIKE '%PICU%' OR AL1.DOCTOR LIKE
'%ROC%' OR AL1.DOCTOR LIKE '%RTC%') AND (NOT AL3.NAME LIKE '%REJ%') AND
AL1.DATE_SERVICE='2005-01-15')
GROUP BY AL3.LAB, AL3.DEPARTMENT, AL6.NAME, AL5.SALES_TERRIOTARY,
AL1.DATE_SERVICE ORDER BY 2"
Your prompt feedback is very much appreciated.

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
In article <11**********************@g44g2000cwa.googlegroups .com>,
xe******@gmail.com says...
Greetings all,
We have a complicated statement in DB2 which takes long hour to
complete and we have created most of the indexes. Does anybody knows
how to tune the following statement to optimize the query?


I optimized the query to find out what your query is supposed to do.
As you can see below the only restrictive entry in your where clause is
in row 74: AL1.DATE_SERVICE='2005-01-15'.
Check that you have indexes on all columns mentioned in rows 13 - 19 and
that your statistics are up to date. You might need to run runstats with
the 'WITH DISTRIBUTION' option on the YR2005.REQUESTX table.

1. SELECT AL3.LAB,
2. AL3.DEPARTMENT,
3. AL6.NAME,
4. Count (AL4.ITEM),
5. AL5.SALES_TERRIOTARY,
6. AL1.DATE_SERVICE
7. FROM YR2005.REQUESTX AL1,
8. YR2005.RESULTP AL2,
9. YR2005.PANEL AL3,
10. YR2005.RESULTV AL4,
11. YR2005.DOCTOR AL5,
12. YR2005.DEPT AL6
13. WHERE ( AL2.REQUEST=AL1.RELREC
14. AND AL3.CODE=AL2.PANEL
15. AND AL3.LAB=AL1.LAB
16. AND AL4.RESULTP=AL2.RELREC
17. AND AL1.DOCTOR=AL5.CODE
18. AND AL3.DEPARTMENT=AL6.CODE
19. AND AL3.LAB=AL6.LAB)
20. AND ( (AL1.LAB LIKE '%KLG%' OR AL1.LAB LIKE '%OPD%')
21. AND (NOT AL4.ALPHA='/')
22. AND ( AL1.DOCTOR LIKE '%10%'
23. OR AL1.DOCTOR LIKE '%11%'
24. OR AL1.DOCTOR LIKE '%12%'
25. OR AL1.DOCTOR LIKE '%13%'
26. OR AL1.DOCTOR LIKE '%14%'
27. OR AL1.DOCTOR LIKE '%15%'
28. OR AL1.DOCTOR LIKE '%16%'
29. OR AL1.DOCTOR LIKE '%18%'
30. OR AL1.DOCTOR LIKE '%1A%'
31. OR AL1.DOCTOR LIKE '%21%'
32. OR AL1.DOCTOR LIKE '%23%'
33. OR AL1.DOCTOR LIKE '%24%'
34. OR AL1.DOCTOR LIKE '%25%'
35. OR AL1.DOCTOR LIKE '%26%'
36. OR AL1.DOCTOR LIKE '%27%'
37. OR AL1.DOCTOR LIKE '%28%'
38. OR AL1.DOCTOR LIKE '%2A%'
39. OR AL1.DOCTOR LIKE '%3%'
40. OR AL1.DOCTOR LIKE '%3A%'
41. OR AL1.DOCTOR LIKE '%3B%'
42. OR AL1.DOCTOR LIKE '%4B%'
43. OR AL1.DOCTOR LIKE '%9%'
44. OR AL1.DOCTOR LIKE '%ANC%'
45. OR AL1.DOCTOR LIKE '%CCU%'
46. OR AL1.DOCTOR LIKE '%GICU%'
47. OR AL1.DOCTOR LIKE '%GOT%'
48. OR AL1.DOCTOR LIKE '%HDW%'
49. OR AL1.DOCTOR LIKE '%ICU%'
50. OR AL1.DOCTOR LIKE '%K4%'
51. OR AL1.DOCTOR LIKE '%K7%'
52. OR AL1.DOCTOR LIKE '%KK1%'
53. OR AL1.DOCTOR LIKE '%KK2%'
54. OR AL1.DOCTOR LIKE '%KK3%'
55. OR AL1.DOCTOR LIKE '%KK5%'
56. OR AL1.DOCTOR LIKE '%KK6%'
57. OR AL1.DOCTOR LIKE '%KK7%'
58. OR AL1.DOCTOR LIKE '%KK8%'
59. OR AL1.DOCTOR LIKE '%KK9%'
60. OR AL1.DOCTOR LIKE '%KPKK %'
61. OR AL1.DOCTOR LIKE '%LR%'
62. OR AL1.DOCTOR LIKE '%MH%'
63. OR AL1.DOCTOR LIKE '%MPC%'
64. OR AL1.DOCTOR LIKE '%P1%'
65. OR AL1.DOCTOR LIKE '%P10%'
66. OR AL1.DOCTOR LIKE '%P2%'
67. OR AL1.DOCTOR LIKE '%P8%'
68. OR AL1.DOCTOR LIKE '%P9%'
69. OR AL1.DOCTOR LIKE '%PC%'
70. OR AL1.DOCTOR LIKE '%PICU%'
71. OR AL1.DOCTOR LIKE '%ROC%'
72. OR AL1.DOCTOR LIKE '%RTC%')
73. AND (NOT AL3.NAME LIKE '%REJ%')
74. AND AL1.DATE_SERVICE='2005-01-15')
75. GROUP BY AL3.LAB,
76. AL3.DEPARTMENT,
77. AL6.NAME,
78. AL5.SALES_TERRIOTARY,
79. AL1.DATE_SERVICE
80. ORDER BY 2
Nov 12 '05 #2

P: n/a

<xe******@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Greetings all,
We have a complicated statement in DB2 which takes long hour to
complete and we have created most of the indexes. Does anybody knows
how to tune the following statement to optimize the query?

"SELECT AL3.LAB, AL3.DEPARTMENT, AL6.NAME, Count (AL4.ITEM),
AL5.SALES_TERRIOTARY, AL1.DATE_SERVICE
FROM YR2005.REQUESTX AL1, YR2005.RESULTP AL2, YR2005.PANEL AL3,
YR2005.RESULTV AL4, YR2005.DOCTOR AL5, YR2005.DEPT AL6
WHERE (AL2.REQUEST=AL1.RELREC AND AL3.CODE=AL2.PANEL AND
AL3.LAB=AL1.LAB AND AL4.RESULTP=AL2.RELREC AND AL1.DOCTOR=AL5.CODE
AND AL3.DEPARTMENT=AL6.CODE AND AL3.LAB=AL6.LAB) AND ((AL1.LAB LIKE
'%KLG%' OR AL1.LAB LIKE '%OPD%') AND (NOT AL4.ALPHA='/')
AND (AL1.DOCTOR LIKE '%10%' OR AL1.DOCTOR LIKE '%11%' OR AL1.DOCTOR
LIKE '%12%' OR AL1.DOCTOR LIKE '%13%' OR AL1.DOCTOR
LIKE '%14%' OR AL1.DOCTOR LIKE '%15%' OR AL1.DOCTOR LIKE '%16%' OR
AL1.DOCTOR LIKE '%18%' OR AL1.DOCTOR LIKE '%1A%' OR AL1.DOCTOR
LIKE '%21%' OR AL1.DOCTOR LIKE '%23%' OR AL1.DOCTOR LIKE '%24%' OR
AL1.DOCTOR LIKE '%25%' OR AL1.DOCTOR LIKE '%26%' OR AL1.DOCTOR
LIKE '%27%' OR AL1.DOCTOR LIKE '%28%' OR AL1.DOCTOR LIKE '%2A%' OR
AL1.DOCTOR LIKE '%3%' OR AL1.DOCTOR LIKE '%3A%' OR AL1.DOCTOR
LIKE '%3B%' OR AL1.DOCTOR LIKE '%4B%' OR AL1.DOCTOR LIKE '%9%' OR
AL1.DOCTOR LIKE '%ANC%' OR AL1.DOCTOR LIKE '%CCU%' OR AL1.DOCTOR
LIKE '%GICU%' OR AL1.DOCTOR LIKE '%GOT%' OR AL1.DOCTOR LIKE '%HDW%' OR
AL1.DOCTOR LIKE '%ICU%' OR AL1.DOCTOR LIKE '%K4%' OR AL1.DOCTOR LIKE
'%K7%' OR AL1.DOCTOR LIKE '%KK1%' OR AL1.DOCTOR LIKE '%KK2%' OR
AL1.DOCTOR LIKE '%KK3%' OR AL1.DOCTOR LIKE '%KK5%' OR AL1.DOCTOR LIKE
'%KK6%' OR AL1.DOCTOR LIKE '%KK7%' OR AL1.DOCTOR LIKE '%KK8%' OR
AL1.DOCTOR LIKE '%KK9%' OR AL1.DOCTOR LIKE '%KPKK %' OR AL1.DOCTOR LIKE
'%LR%' OR AL1.DOCTOR LIKE '%MH%' OR AL1.DOCTOR LIKE '%MPC%' OR
AL1.DOCTOR LIKE '%P1%' OR AL1.DOCTOR LIKE '%P10%' OR AL1.DOCTOR LIKE
'%P2%' OR AL1.DOCTOR LIKE '%P8%' OR AL1.DOCTOR LIKE '%P9%' OR
AL1.DOCTOR LIKE '%PC%' OR AL1.DOCTOR LIKE '%PICU%' OR AL1.DOCTOR LIKE
'%ROC%' OR AL1.DOCTOR LIKE '%RTC%') AND (NOT AL3.NAME LIKE '%REJ%') AND
AL1.DATE_SERVICE='2005-01-15')
GROUP BY AL3.LAB, AL3.DEPARTMENT, AL6.NAME, AL5.SALES_TERRIOTARY,
AL1.DATE_SERVICE ORDER BY 2"
Your prompt feedback is very much appreciated.

Is there any way you could modify the data so that the _known_ part of the
AL1.DOCTOR column was at the extreme left of the column? The wild card
characters in all of those LIKE predicates is probably what's killing you.
If you could store the data in the AL1.DOCTOR column so that the known data
("11", "16" etc.) was on the extreme left, then your LIKE predicates would
change from this pattern:

AL1.DOCTOR LIKE ("%16")

to this one:

AL1.DOCTOR LIKE ("16%")

I think that would do a lot to improve your performance. Wild cards at the
left of the LIKE pattern always cause a non-matching index scan (at best) so
getting rid of that card would greatly increase the chances of getting a
matching index scan, which normally performs better.

Rhino

Nov 12 '05 #3

P: n/a
Or, if these changes are significant, make a GENERATED COLUMN that hold
the important part of the data, and base the queries on that.

B.

Nov 12 '05 #4

P: n/a
It's possible that clustering the REQUESTX table on DATE_SERVICE will
encourage the optimizer to use that predicate first. That may
significantly improve performance. If it does, you'll have to adopt a
scheme to keep the table well clustered.

Phil Sherman

xe******@gmail.com wrote:
Greetings all,
We have a complicated statement in DB2 which takes long hour to
complete and we have created most of the indexes. Does anybody knows
how to tune the following statement to optimize the query?

"SELECT AL3.LAB, AL3.DEPARTMENT, AL6.NAME, Count (AL4.ITEM),
AL5.SALES_TERRIOTARY, AL1.DATE_SERVICE
FROM YR2005.REQUESTX AL1, YR2005.RESULTP AL2, YR2005.PANEL AL3,
YR2005.RESULTV AL4, YR2005.DOCTOR AL5, YR2005.DEPT AL6
WHERE (AL2.REQUEST=AL1.RELREC AND AL3.CODE=AL2.PANEL AND
AL3.LAB=AL1.LAB AND AL4.RESULTP=AL2.RELREC AND AL1.DOCTOR=AL5.CODE
AND AL3.DEPARTMENT=AL6.CODE AND AL3.LAB=AL6.LAB) AND ((AL1.LAB LIKE
'%KLG%' OR AL1.LAB LIKE '%OPD%') AND (NOT AL4.ALPHA='/')
AND (AL1.DOCTOR LIKE '%10%' OR AL1.DOCTOR LIKE '%11%' OR AL1.DOCTOR
LIKE '%12%' OR AL1.DOCTOR LIKE '%13%' OR AL1.DOCTOR
LIKE '%14%' OR AL1.DOCTOR LIKE '%15%' OR AL1.DOCTOR LIKE '%16%' OR
AL1.DOCTOR LIKE '%18%' OR AL1.DOCTOR LIKE '%1A%' OR AL1.DOCTOR
LIKE '%21%' OR AL1.DOCTOR LIKE '%23%' OR AL1.DOCTOR LIKE '%24%' OR
AL1.DOCTOR LIKE '%25%' OR AL1.DOCTOR LIKE '%26%' OR AL1.DOCTOR
LIKE '%27%' OR AL1.DOCTOR LIKE '%28%' OR AL1.DOCTOR LIKE '%2A%' OR
AL1.DOCTOR LIKE '%3%' OR AL1.DOCTOR LIKE '%3A%' OR AL1.DOCTOR
LIKE '%3B%' OR AL1.DOCTOR LIKE '%4B%' OR AL1.DOCTOR LIKE '%9%' OR
AL1.DOCTOR LIKE '%ANC%' OR AL1.DOCTOR LIKE '%CCU%' OR AL1.DOCTOR
LIKE '%GICU%' OR AL1.DOCTOR LIKE '%GOT%' OR AL1.DOCTOR LIKE '%HDW%' OR
AL1.DOCTOR LIKE '%ICU%' OR AL1.DOCTOR LIKE '%K4%' OR AL1.DOCTOR LIKE
'%K7%' OR AL1.DOCTOR LIKE '%KK1%' OR AL1.DOCTOR LIKE '%KK2%' OR
AL1.DOCTOR LIKE '%KK3%' OR AL1.DOCTOR LIKE '%KK5%' OR AL1.DOCTOR LIKE
'%KK6%' OR AL1.DOCTOR LIKE '%KK7%' OR AL1.DOCTOR LIKE '%KK8%' OR
AL1.DOCTOR LIKE '%KK9%' OR AL1.DOCTOR LIKE '%KPKK %' OR AL1.DOCTOR LIKE
'%LR%' OR AL1.DOCTOR LIKE '%MH%' OR AL1.DOCTOR LIKE '%MPC%' OR
AL1.DOCTOR LIKE '%P1%' OR AL1.DOCTOR LIKE '%P10%' OR AL1.DOCTOR LIKE
'%P2%' OR AL1.DOCTOR LIKE '%P8%' OR AL1.DOCTOR LIKE '%P9%' OR
AL1.DOCTOR LIKE '%PC%' OR AL1.DOCTOR LIKE '%PICU%' OR AL1.DOCTOR LIKE
'%ROC%' OR AL1.DOCTOR LIKE '%RTC%') AND (NOT AL3.NAME LIKE '%REJ%') AND
AL1.DATE_SERVICE='2005-01-15')
GROUP BY AL3.LAB, AL3.DEPARTMENT, AL6.NAME, AL5.SALES_TERRIOTARY,
AL1.DATE_SERVICE ORDER BY 2"
Your prompt feedback is very much appreciated.

Nov 12 '05 #5

P: n/a
Hi all,
Thanks for all your reply. Finally, I managed to optimize the query
based of some of your suggestion and by tuning of these variables:
1)DBHEAP
2)LOGFILSZ
3)BUFFPAGE
4)SORTHEAP
5)DFT_QUERYOPT -> 5 to 1

Appreciate for all your suggestion.
Phil Sherman wrote:
It's possible that clustering the REQUESTX table on DATE_SERVICE will
encourage the optimizer to use that predicate first. That may
significantly improve performance. If it does, you'll have to adopt a
scheme to keep the table well clustered.

Phil Sherman

xe******@gmail.com wrote:
Greetings all,
We have a complicated statement in DB2 which takes long hour to
complete and we have created most of the indexes. Does anybody knows
how to tune the following statement to optimize the query?

"SELECT AL3.LAB, AL3.DEPARTMENT, AL6.NAME, Count (AL4.ITEM),
AL5.SALES_TERRIOTARY, AL1.DATE_SERVICE
FROM YR2005.REQUESTX AL1, YR2005.RESULTP AL2, YR2005.PANEL AL3,
YR2005.RESULTV AL4, YR2005.DOCTOR AL5, YR2005.DEPT AL6
WHERE (AL2.REQUEST=AL1.RELREC AND AL3.CODE=AL2.PANEL AND
AL3.LAB=AL1.LAB AND AL4.RESULTP=AL2.RELREC AND AL1.DOCTOR=AL5.CODE
AND AL3.DEPARTMENT=AL6.CODE AND AL3.LAB=AL6.LAB) AND ((AL1.LAB LIKE
'%KLG%' OR AL1.LAB LIKE '%OPD%') AND (NOT AL4.ALPHA='/')
AND (AL1.DOCTOR LIKE '%10%' OR AL1.DOCTOR LIKE '%11%' OR AL1.DOCTOR
LIKE '%12%' OR AL1.DOCTOR LIKE '%13%' OR AL1.DOCTOR
LIKE '%14%' OR AL1.DOCTOR LIKE '%15%' OR AL1.DOCTOR LIKE '%16%' OR
AL1.DOCTOR LIKE '%18%' OR AL1.DOCTOR LIKE '%1A%' OR AL1.DOCTOR
LIKE '%21%' OR AL1.DOCTOR LIKE '%23%' OR AL1.DOCTOR LIKE '%24%' OR
AL1.DOCTOR LIKE '%25%' OR AL1.DOCTOR LIKE '%26%' OR AL1.DOCTOR
LIKE '%27%' OR AL1.DOCTOR LIKE '%28%' OR AL1.DOCTOR LIKE '%2A%' OR
AL1.DOCTOR LIKE '%3%' OR AL1.DOCTOR LIKE '%3A%' OR AL1.DOCTOR
LIKE '%3B%' OR AL1.DOCTOR LIKE '%4B%' OR AL1.DOCTOR LIKE '%9%' OR
AL1.DOCTOR LIKE '%ANC%' OR AL1.DOCTOR LIKE '%CCU%' OR AL1.DOCTOR
LIKE '%GICU%' OR AL1.DOCTOR LIKE '%GOT%' OR AL1.DOCTOR LIKE '%HDW%' OR
AL1.DOCTOR LIKE '%ICU%' OR AL1.DOCTOR LIKE '%K4%' OR AL1.DOCTOR LIKE
'%K7%' OR AL1.DOCTOR LIKE '%KK1%' OR AL1.DOCTOR LIKE '%KK2%' OR
AL1.DOCTOR LIKE '%KK3%' OR AL1.DOCTOR LIKE '%KK5%' OR AL1.DOCTOR LIKE
'%KK6%' OR AL1.DOCTOR LIKE '%KK7%' OR AL1.DOCTOR LIKE '%KK8%' OR
AL1.DOCTOR LIKE '%KK9%' OR AL1.DOCTOR LIKE '%KPKK %' OR AL1.DOCTOR LIKE
'%LR%' OR AL1.DOCTOR LIKE '%MH%' OR AL1.DOCTOR LIKE '%MPC%' OR
AL1.DOCTOR LIKE '%P1%' OR AL1.DOCTOR LIKE '%P10%' OR AL1.DOCTOR LIKE
'%P2%' OR AL1.DOCTOR LIKE '%P8%' OR AL1.DOCTOR LIKE '%P9%' OR
AL1.DOCTOR LIKE '%PC%' OR AL1.DOCTOR LIKE '%PICU%' OR AL1.DOCTOR LIKE
'%ROC%' OR AL1.DOCTOR LIKE '%RTC%') AND (NOT AL3.NAME LIKE '%REJ%') AND
AL1.DATE_SERVICE='2005-01-15')
GROUP BY AL3.LAB, AL3.DEPARTMENT, AL6.NAME, AL5.SALES_TERRIOTARY,
AL1.DATE_SERVICE ORDER BY 2"
Your prompt feedback is very much appreciated.


Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.