Performance Tuning [message #618319] |
Thu, 10 July 2014 11:47 |
|
andrewscharles89
Messages: 25 Registered: July 2014
|
Junior Member |
|
|
we have a query with large union all with a lot of tables. If that query gets hung up, it will hold upalter statements on those tables
we should break the query up into individual ones to minimize that effect in the future. Does anyone have any thoughts/ideas on it ?
|
|
|
Re: Performance Tuning [message #618321 is a reply to message #618319] |
Thu, 10 July 2014 11:56 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
I tried to find the query(which I can't see) in your post, I get no clues how to help you.
Please read the sticky on top of this forum. If it doesn't help, moderators would point you to further links to make a sensible post
Edit : typo
[Updated on: Thu, 10 July 2014 11:57] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: Performance Tuning [message #618409 is a reply to message #618327] |
Fri, 11 July 2014 09:31 |
|
andrewscharles89
Messages: 25 Registered: July 2014
|
Junior Member |
|
|
[code]SELECT MODEL_ID, COUNT(*) FROM MOD_IN_MLTLVL_AA WHERE SCENARIOS_KEY = (SELECT MEMBER_KEY FROM HRY_SCN_CURR WHERE SCENARIOS = '1415.C.JUL.BF') AND ACCOUNT_KEY IN (SELECT MEMBER_KEY FROM OVW_ALL_ACCOUNTS WHERE (MEMBER_ID LIKE '%84240000%' OR MEMBER_ID LIKE '%84330000%' OR MEMBER_ID LIKE '%84280000%' OR MEMBER_ID LIKE '%84370000%' OR MEMBER_ID LIKE '%84250000%' OR MEMBER_ID LIKE '%84340000%' OR MEMBER_ID LIKE '%84290000%' OR MEMBER_ID LIKE '%84380000%' OR MEMBER_ID LIKE '%84260000%' OR MEMBER_ID LIKE '%84350000%' OR MEMBER_ID LIKE '%84300000%' OR MEMBER_ID LIKE '%84390000%' OR MEMBER_ID LIKE '%81013112%' OR MEMBER_ID LIKE '%81013113%' OR MEMBER_ID LIKE '%81013341%' OR MEMBER_ID LIKE '%81013346%' OR MEMBER_ID LIKE '%81013335%' OR MEMBER_ID LIKE '%81013336%' OR MEMBER_ID LIKE '%81013340%' OR MEMBER_ID LIKE '%81013345%') )union all
SELECT MODEL_ID, COUNT(*) FROM MOD_IN_MLTLVL_AB WHERE SCENARIOS_KEY = (SELECT MEMBER_KEY FROM HRY_SCN_CURR WHERE SCENARIOS = '1415.C.JUL.BF') AND ACCOUNT_KEY IN (SELECT MEMBER_KEY FROM OVW_ALL_ACCOUNTS WHERE (MEMBER_ID LIKE '%84240000%' OR MEMBER_ID LIKE '%84330000%' OR MEMBER_ID LIKE '%84280000%' OR MEMBER_ID LIKE '%84370000%' OR MEMBER_ID LIKE '%84250000%' OR MEMBER_ID LIKE '%84340000%' OR MEMBER_ID LIKE '%84290000%' OR MEMBER_ID LIKE '%84380000%' OR MEMBER_ID LIKE '%84260000%' OR MEMBER_ID LIKE '%84350000%' OR MEMBER_ID LIKE '%84300000%' OR MEMBER_ID LIKE '%84390000%' OR MEMBER_ID LIKE '%81013112%' OR MEMBER_ID LIKE '%81013113%' OR MEMBER_ID LIKE '%81013341%' OR MEMBER_ID LIKE '%81013346%' OR MEMBER_ID LIKE '%81013335%' OR MEMBER_ID LIKE '%81013336%' OR MEMBER_ID LIKE '%81013340%' OR MEMBER_ID LIKE '%81013345%') )union all[/code]
...
..
..
the query goes on like this for next 180 lines with union all
the only change is the table name in outer query, the sub query and the values in the sub query are all same
[Updated on: Fri, 11 July 2014 09:31] Report message to a moderator
|
|
|
Re: Performance Tuning [message #618412 is a reply to message #618409] |
Fri, 11 July 2014 09:37 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Use a with clause for the sub-queries.
Real question is, should all the MOD_IN_MLTLVL... tables exist, or would you be a lot better off if they were combined into a single table.
Partitioning data across separate tables is fine up until the point where you need to combine them.
|
|
|
|
Re: Performance Tuning [message #619437 is a reply to message #618463] |
Tue, 22 July 2014 08:20 |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
1. As cookiemonster already wrote - Use WITH clause to reduce the number of accesses from HRY_SCN_CURR and OVW_ALL_ACCOUNTS tables/views
WITH MEMBER_LIST AS (SELECT S.MEMBER_KEY AS SCENARIOS_KEY, A.MEMBER_KEY AS ACCOUNT_KEY
FROM OVW_ALL_ACCOUNTS A, HRY_SCN_CURR S
WHERE S.SCENARIOS = '1415.C.JUL.BF' AND
(A.MEMBER_ID LIKE '%84240000%' OR A.MEMBER_ID LIKE '%84330000%' OR A.MEMBER_ID LIKE '%84280000%' OR
A.MEMBER_ID LIKE '%84370000%' OR A.MEMBER_ID LIKE '%84250000%' OR A.MEMBER_ID LIKE '%84340000%' OR
A.MEMBER_ID LIKE '%84290000%' OR A.MEMBER_ID LIKE '%84380000%' OR A.MEMBER_ID LIKE '%84260000%' OR
A.MEMBER_ID LIKE '%84350000%' OR A.MEMBER_ID LIKE '%84300000%' OR A.MEMBER_ID LIKE '%84390000%' OR
A.MEMBER_ID LIKE '%81013112%' OR A.MEMBER_ID LIKE '%81013113%' OR A.MEMBER_ID LIKE '%81013341%' OR
A.MEMBER_ID LIKE '%81013346%' OR A.MEMBER_ID LIKE '%81013335%' OR A.MEMBER_ID LIKE '%81013336%' OR
A.MEMBER_ID LIKE '%81013340%' OR A.MEMBER_ID LIKE '%81013345%') )
SELECT AA.MODEL_ID, COUNT(*)
FROM MEMBER_LIST M
JOIN MOD_IN_MLTLVL_AA AA ON AA.SCENARIOS_KEY = M.SCENARIOS_KEY AND AA.ACCOUNT_KEY = M.ACCOUNT_KEY
union all
SELECT AB.MODEL_ID, COUNT(*)
FROM MEMBER_LIST M
JOIN MOD_IN_MLTLVL_AB AB ON AB.SCENARIOS_KEY = M.SCENARIOS_KEY AND AB.ACCOUNT_KEY = M.ACCOUNT_KEY
...
2. How many rows are selected from OVW_ALL_ACCOUNTS table (with all these ORs)?
3. Do you have indexes on all MOD_IN... tables on ( SCENARIOS_KEY, ACCOUNT_KEY ) pair?
4. Post EXPLAIN/ TKPROF.
HTH
|
|
|