Why is there such a big difference between performance of those two queries?

user_6385972 0
Viewed 137

I had a task to get specific accounts and transactions based on few rules. It's worth noting that accounts and transactions are in different tables and my query returns mostly accounts - proportions are around 70 accounts to 1 transaction. I wanted to get them in one query for convenience sake - it's a stage in bigger process.

Original query:

SELECT DISTINCT 
    CASE 
        WHEN (a.transaction_type IN ('500', '501', '502', '920') AND a.transaction_date >= '#DATE#' AND to_char(a.transaction_date,'HH24') >= 16) THEN 'Transaction time'
        WHEN b.closing_date >= '#DATE#' THEN 'Closing time'
        WHEN b.opening_date >= '#DATE#'    THEN 'Opening time'  
        WHEN (b.type = 'X' AND b.active = 'NO') THEN 'Frozen account'   
    END AS "comment"
    ,b.branch 
    ,b.basic 
    ,b.lmt 
FROM 
    VDS.transactions a
    JOIN VDS.accounts b ON a.acct_no = b.acct_no
WHERE
    (a.transaction_type IN ('500', '501', '502', '920')
    AND a.transaction_date  >= '#DATE#'
    AND to_char(a.transaction_date,'HH24') >= 16)
    OR
    (b.closing_date >= '#DATE#'
    OR b.opening_date >= '#DATE#'   
    OR (b.type = 'X' AND b.active = 'NO'))

It seemed to work fine even if somewhat sluggish - its execution time was usually somewhere around 12s. Problem is - sometimes it wouldn't finish at all. It looked like the database got completely stuck on the query. Since I'm not an Oracle admin I can't confirm my suspicion that it's this query's fault but multiple tests suggest that it indeed is.

So I have prepared another variant, taking into account that transactions are way less numerous than accounts.

Variant with subquery:

SELECT 
    'Transaction time' AS "comment"
    ,b.branch 
    ,b.basic 
    ,b.lmt 
FROM 
    VDS.transactions a
    JOIN VDS.accounts b ON a.acct_no = b.acct_no
WHERE
    a.transaction_type IN ('500', '501', '502', '920')
    AND a.transaction_date  >= '#DATE#'
    AND to_char(a.transaction_date,'HH24') >= 16

UNION

SELECT 
    CASE 
        WHEN closing_date >= '#DATE#' THEN 'Closing time'
        WHEN opening_date >= '#DATE#'    THEN 'Opening time'    
        WHEN (type = 'X' AND active = 'NO') THEN 'Frozen account'   
    END AS "comment"
    ,branch 
    ,basic 
    ,lmt 
FROM 
    VDS.accounts 
WHERE
    closing_date >= '#DATE#'
    OR opening_date >= '#DATE#' 
    OR (type = 'X' AND active = 'NO'))

Lo and behold - execution times were reduced to around 3-5s and query no longer blocked database. It also returned slightly more results which is strange but not a problem.

So my final question is: Can someone explain to me what might've been going in the guts of database that it happily accepted variant with subquery while getting erratic with original one? I can understand better performance with subquery but I have no idea why query would sometimes work and sometimes hang up entirely.

query-performance oracle sql
3 Answers

Actually, it makes complete sense... Lets look at your first query's WHERE clause

WHERE
    (a.transaction_type IN ('500', '501', '502', '920')
    AND a.transaction_date  >= '#DATE#'
    AND to_char(a.transaction_date,'HH24') >= 16)
    OR
    (b.closing_date >= '#DATE#'
    OR b.opening_date >= '#DATE#'   
    OR (b.type = 'X' AND b.active = 'NO'))

First part is simple... transactions that are >= a given date and of a certain transaction type. No problem.

NOW, you add an OR to ANY of the other criteria of your "B" accounts table testing for opened,closed or frozen accounts.

Because of the (transactions) OR (accounts), you have opened the query up to ALL TRANSACTIONS (due to the OR) as you are comparing the final "OR" clause for the freeze condition that has no care about a date/time.

Since a transaction would happen on an account to open OR close an account, you KNOW the account will have the activity going on. If an ATTEMPT at a transaction against a frozen account, that too will be accounted for.

Here is how I would adjust your where clause...

WHERE
        a.transaction_date  >= '#DATE#'
    AND to_char(a.transaction_date,'HH24') >= 16)
    AND 
        (  a.transaction_type IN ('500', '501', '502', '920')
             OR
           (   b.closing_date >= '#DATE#'
            OR b.opening_date >= '#DATE#'   
            OR (b.type = 'X' AND b.active = 'NO')
        )

So you are only ever considering transaction within the expected date period... and of those, only those with the transaction type OR (open, closed, frozen)

user_741950

Your second query will return multiple results from the accounts table where a single row has been duplicated with different comments (where it has been match by both the sub-queries above and below the UNION).

However, in the second query, each sub-query is more efficient as the first sub-query can filter on the transaction table and only perform the JOIN when the filter has been matched and the second sub-query can filter on the account table and needs no JOIN so, between the two, there are no unnecessary JOINs needed. This is not the case for the first query as the CASE statement uses values from both account and transaction tables so all the rows have to be JOINed regardless of which filter conditions were matched.

Another alternative may be to not use a JOIN and use EXISTS instead and rely on the ELSE clause of the CASE statement to correspond with when the EXISTS filter was matched and the other OR filters were not matched.

SELECT CASE 
       WHEN closing_date >= '#DATE#'
       THEN 'Closing time'
       WHEN opening_date >= '#DATE#'
       THEN 'Opening time'    
       WHEN (type = 'X' AND active = 'NO')
       THEN 'Frozen account'
       ELSE 'Transaction time'
       END AS comment,
       branch,
       basic,
       lmt 
FROM   VDS.accounts a
WHERE  closing_date >= '#DATE#'
OR     opening_date >= '#DATE#' 
OR     ( type = 'X' AND active = 'NO')
OR     EXISTS (
         SELECT 1
         FROM   VDS.transactions t
         WHERE  a.acct_no = t.acct_no
         AND    t.transaction_type IN ('500', '501', '502', '920')
         AND    t.transaction_date  >= '#DATE#'
         AND    to_char(t.transaction_date,'HH24') >= 16
       )

Note: This has a different priority in the CASE statement so the comment may give different values to your query - they should be a sub-set of the second query's returned rows but may not match with the first query.

user_15092640

The first form of the query uses an OR with conditions only on the accounts table, so it must read every transaction that has a matching account even if they are not used.

In the second form of the query the transaction query can use indexes on transaction_type and transaction_date if there are any (which seems likely given the domain) and transactions that don't match can be skipped entirely.

Without an explain it is guesswork, but the first query needs to read more data and probably cannot use indexes as well as the second.

You say that the query returns more accounts than transactions. That may be so, but I suspect that in total there are many more transactions than accounts? And as the first form of the query probably needs to scan the transactions table it doesn't matter if they match or not, it matters how large the table is.

user_26120300
Related