Large Query - Is UNION ALL the best option?

DBFinder · #1 (**permalink**) 05-20-09, 16:23

Hello,

Can some one help me decide if the query can use some other thing to improve perf.

Code:

SELECT total_bets, credited_bets, total_payouts, game_type, game_code
FROM 
(
SELECT SUM(total_bets) AS total_bets, SUM(credited_bets) AS credited_bets, SUM(total_payouts) AS total_payouts, game_type AS game_type, game_code AS game_code, casino_id AS casino_id
FROM 
(
	SELECT SUM(bets) AS total_bets, SUM(bets * (COALESCE(percentage, 0)/100.0)) AS credited_bets, SUM(payouts) AS total_payouts, cg.display_type AS game_type, cg.component_id AS game_code, SUBSTR(acc_num, 1, 2) AS casino_id
	FROM player_stats p INNER JOIN component_group cg ON cg.group_id = p.group_id
	LEFT OUTER JOIN component_group_wager w ON p.group_id = w.group_id AND SUBSTR(acc_num, 1, 2) = w.merchant 
	WHERE  acc_num = 'TT0845162881' AND DATE(TIMESTAMP) >= DATE('1999-01-01-00.00.00') AND DATE(TIMESTAMP) < DATE(CURRENT TIMESTAMP - 1 DAY)
	GROUP BY cg.display_type, cg.component_id, acc_num
UNION ALL
	SELECT SUM(bet_amt) AS total_bets, SUM(bet_amt * (COALESCE(percentage, 0)/100.0)) AS credited_bets, SUM(win_amt) AS total_payouts, cg.display_type AS game_type, pgs.component_id AS game_code, SUBSTR(acc_num, 1, 2) AS casino_id
	FROM player_g_session pgs INNER JOIN component_group cg ON cg.group_id = pgs.group_id
	LEFT OUTER JOIN component_group_wager w ON pgs.group_id = w.group_id AND SUBSTR(acc_num, 1, 2) = w.merchant 
	WHERE  acc_num = 'TT0845162881' AND timestamp >= 
	(SELECT CASE WHEN TIMESTAMP('1999-01-01-00.00.00') > TIMESTAMP(CURRENT DATE - 1 DAY, '00.00.00') THEN TIMESTAMP('1999-01-01-00.00.00') ELSE TIMESTAMP(CURRENT DATE - 1 DAY, '00.00.00') END FROM CLIENT_ACC FETCH FIRST 1 ROW ONLY)
	AND timestamp < TIMESTAMP(DATE(CURRENT TIMESTAMP - 1 HOUR), RTRIM(CHAR(HOUR(CURRENT TIMESTAMP -1 HOUR))) || '.00.00')
	GROUP BY cg.display_type, pgs.component_id, acc_num
UNION ALL
	SELECT SUM(bet_amt) AS total_bets, SUM(bet_amt * (COALESCE(percentage, 0)/100.0)) AS credited_bets, SUM(win_amt) AS total_payouts, cg.display_type AS game_type, cg.component_id AS game_code, SUBSTR(acc_num, 1, 2) AS casino_id
	FROM game_trans g INNER JOIN blackjack_d b ON g.game_trans_id = b.game_trans_id 
	LEFT OUTER JOIN component_group cg ON cg.group_id = g.group_id
	LEFT OUTER JOIN component_group_wager w ON b.group_id = w.group_id AND SUBSTR(acc_num, 1, 2) = w.merchant 
	WHERE mode = 'R' AND player = 9 AND (b.ended = 'TRUE' OR b.ended IS NULL ) AND acc_num = 'TT0845162881' AND timestamp >= 
	(SELECT CASE WHEN TIMESTAMP('1999-01-01-00.00.00') > TIMESTAMP(DATE(CURRENT TIMESTAMP - 1 HOUR), RTRIM(CHAR(HOUR(TIME(CURRENT TIMESTAMP -1 HOUR)))) || '.00.00') THEN TIMESTAMP('1999-01-01-00.00.00') ELSE TIMESTAMP(DATE(CURRENT TIMESTAMP - 1 HOUR), RTRIM(CHAR(HOUR(TIME(CURRENT TIMESTAMP -1 HOUR)))) || '.00.00') END FROM CLIENT_ACC FETCH FIRST 1 ROW ONLY)
	GROUP BY cg.display_type, cg.component_id, acc_num
UNION ALL
	SELECT SUM(bet_amt) as total_bets, SUM(bet_amt * (COALESCE(percentage, 0)/100.0)) AS credited_bets, SUM(win_amt) AS total_payouts, cg.display_type AS game_type, cg.component_id AS game_code, SUBSTR(acc_num, 1, 2) AS casino_id
	FROM game_trans g INNER JOIN fivereel_d f ON g.game_trans_id = f.game_trans_id 
	LEFT OUTER JOIN component_group cg ON cg.group_id = g.group_id
	LEFT OUTER JOIN component_group_wager w ON f.group_id = w.group_id AND SUBSTR(acc_num, 1, 2) = w.merchant 
	WHERE mode = 'R' AND acc_num = 'TT0845162881' AND timestamp >= 
	(SELECT CASE WHEN TIMESTAMP('1999-01-01-00.00.00') > TIMESTAMP(DATE(CURRENT TIMESTAMP - 1 HOUR), RTRIM(CHAR(HOUR(TIME(CURRENT TIMESTAMP -1 HOUR)))) || '.00.00') THEN TIMESTAMP('1999-01-01-00.00.00') ELSE TIMESTAMP(DATE(CURRENT TIMESTAMP - 1 HOUR), RTRIM(CHAR(HOUR(TIME(CURRENT TIMESTAMP -1 HOUR)))) || '.00.00') END FROM CLIENT_ACC FETCH FIRST 1 ROW ONLY)
	GROUP BY cg.display_type, cg.component_id, acc_num
UNION ALL
:
:
:

And this one has 22 sections UNIONed ALL together.

This is reported to be slow.

Thanks

DBFinder

n_i · #2 (**permalink**) 05-20-09, 20:03

It may benefit from an MQT. Try running it through db2advis.

Peter.Vanroose · #3 (**permalink**) 05-21-09, 06:32

The "UNION ALL"s all probably fine.
I'd rather go for eliminating all non-sargable (i.e., stage-2) predicates.
For example, "DATE(TIMESTAMP) >= DATE('1999-01-01-00.00.00')" is equivalent to "TIMESTAMP >= TIMESTAMP('1999-01-01-00.00.00')".

It also seems to me that the subquery

Code:

(SELECT CASE
  WHEN TIMESTAMP('1999-01-01-00.00.00')>TIMESTAMP(CURRENT DATE-1 DAY,'00.00.00')
  THEN TIMESTAMP('1999-01-01-00.00.00')
  ELSE TIMESTAMP(CURRENT DATE - 1 DAY, '00.00.00')
 END FROM CLIENT_ACC FETCH FIRST 1 ROW ONLY)

can safely be replaced by

Code:

TIMESTAMP(CURRENT DATE - 1 DAY, '00.00.00')

or otherwise at least remove the dependency on table CLIENT_ACC which need not be accessed, i.e. replace the whole subquery by just the CASE expression.
Or even better: replace the predicate "timestamp >= CASE ... END" by the two predicates

Code:

timestamp >= TIMESTAMP(CURRENT DATE - 1 DAY, '00.00.00')
AND
timestamp >= TIMESTAMP('1999-01-01-00.00.00')

You may also safely replace "SUM(bet_amt * (COALESCE(percentage, 0)/100.0))" by "SUM(0.01*bet_amt*percentage)".
And if there really is always a fixed "AND acc_num = 'TT0845162881'" in all parts (but that's probably just one test case, I presume), you should replace all occurrences of "SUBSTR(acc_num, 1, 2)" by 'TT'.

There are most likely some other optimisations which will improve the preformance drastically, but those require knowledge of the table design and the data statistics.

dr_te_z · #4 (**permalink**) 05-21-09, 07:45

MQT: good advice. You can try the CTE approach like this:

Code:

with cte_temp as 
   ( 
	SELECT bets
             , percentage
             , payouts
             , mode
             , player
             , cg.display_type 
             , cg.component_id 
             , acc_num
  	           FROM player_stats          p 
        INNER      JOIN component_group      cg ON cg.group_id = p.group_id
	LEFT OUTER JOIN component_group_wager w ON  p.group_id = w.group_id 
                                               AND SUBSTR(acc_num, 1, 2) = w.merchant 
        WHERE  acc_num = 'TT0845162881'
   ) 

SELECT total_bets, credited_bets, total_payouts, game_type, game_code
FROM 
(
SELECT SUM(total_bets) AS total_bets, SUM(credited_bets) AS credited_bets, SUM(total_payouts) AS total_payouts, game_type AS game_type, game_code AS game_code, casino_id AS casino_id
FROM 
(
	SELECT SUM(bet_amt) as total_bets
             , SUM(bet_amt * (COALESCE(percentage, 0)/100.0)) AS credited_bets
             , SUM(win_amt) AS total_payouts
             , display_type AS game_type
             , component_id AS game_code
             , SUBSTR(acc_num, 1, 2) AS casino_id

	FROM cte_temp  

	WHERE mode = 'R' 
          AND timestamp >= (
                              SELECT CASE WHEN TIMESTAMP('1999-01-01-00.00.00') > TIMESTAMP(DATE(CURRENT TIMESTAMP - 1 HOUR)
                            , RTRIM(CHAR(HOUR(TIME(CURRENT TIMESTAMP -1 HOUR)))) || '.00.00') 
                              THEN TIMESTAMP('1999-01-01-00.00.00') 
                              ELSE TIMESTAMP(DATE(CURRENT TIMESTAMP - 1 HOUR)
                            , RTRIM(CHAR(HOUR(TIME(CURRENT TIMESTAMP -1 HOUR)))) || '.00.00') END 
                              FROM CLIENT_ACC FETCH FIRST 1 ROW ONLY
                           )
	GROUP BY cg.display_type, cg.component_id, acc_num
UNION ALL 	SELECT ...  
         	FROM cte_temp
	        WHERE ... 
UNION ALL 	SELECT ...  
         	FROM cte_temp
	        WHERE ... 
UNION ALL 	SELECT ...  
         	FROM cte_temp
	        WHERE ...
... 
e_type, wagers.game_code, wagers.casino_id
) sum_wagers
ORDER BY TOTAL_BETS

Selection & joining only done once. Maybe faster, more easy SQL-source maintenance.

DBFinder · #5 (**permalink**) 05-21-09, 11:20

Thanks, peter and dr_te_z

I will try few examples on MQT and advise the dev guys shortly.

DBFinder

Internet Services

Web Development

Digital Marketing

Consumer Tech