Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Large Query - Is UNION ALL the best option?

    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
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It may benefit from an MQT. Try running it through db2advis.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    Last edited by Peter.Vanroose; 05-21-09 at 07:43.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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.
    Last edited by dr_te_z; 05-21-09 at 09:06.

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks, peter and dr_te_z


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


    DBFinder

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •