Same Query, Different Peformance

Question

There is a query that takes more than two minutes. that query's result are 960000 row. so i use hint. then it takes just 30 seconds. so i applied query in mybatis and run application. but that query takes more over two minute. so i copied query in log and paste local developer. and excute query. but it takes just 30 seconds. i don't know........

environment : spring, mybatis, oracle

SELECT * 
FROM (
    SELECT /*+ USE_HASH(a b c) */ ROW_NUMBER() OVER(ORDER BY A.TRANS_DT DESC, A.TRANS_TM DESC) AS RNUM                    
          ,A.PAY_METHOD
          ,B.BRAND_NM  
          ,A.I_MID                                
          ,C.TRANS_DT  
          ,C.TRANS_TM  
          ,C.TRANS_DT || C.TRANS_TM AS TRANS_DTM   
          ,C.VACCT_VALID_DT                        
          ,C.VACCT_VALID_TM                        
          ,C.VACCT_VALID_DT||C.VACCT_VALID_TM AS VACCT_VALID_DTM 
          ,NVL(C.DEPOSIT_DT, ' ') DEPOSIT_DT                     
          ,NVL(C.DEPOSIT_TM, ' ') DEPOSIT_TM                     
          ,NVL(C.DEPOSIT_DT, ' ')||NVL(C.DEPOSIT_TM, ' ') AS DEPOSIT_DTM    
          ,NVL(C.DEPOSIT_AMT, 0) AS AMT                                     
          ,NVL(A.AMT, 0) AS INPUT_AMT                                       
          ,A.BANK_CD                                                        
          ,IONPAY.UF_GET_BANK_NAME(A.BANK_CD) AS BANK_CD_NM                 
          ,C.VACCT_NO                                                  
          ,A.BILLING_NM                                           
          ,A.REFERENCE_NO                                    
          ,A.TXID                                        
          ,A.STATUS         
          ,NVL(A.STATUS, ' ') AS INPUT_STATUS    
          ,IONPAY.UF_GET_TRANS_VACCT_STATUS_NAME(C.STATUS) AS INPUT_STATUS_NAME
          ,C.STATUS AS TRANS_STATUS  
          ,IONPAY.UF_GET_TRANS_VACCT_STA_NAME_2(C.STATUS, C.MATCH_CL, C.VACCT_VALID_DT, C.VACCT_VALID_TM) AS STATUS_NAME 
          ,A.ACQU_STATUS                 
          ,A.CANCEL_DT||A.CANCEL_TM AS REVERSAL_DATE 
          ,NVL((SELECT DESC2
                    FROM TB_CODE
                    WHERE CODE_CL = 'CHNL'
                      AND CODE1 = C.BANK_CD
                      AND CODE2 = C.CHANNEL_TYPE), ' ') AS channel
    FROM TB_TRANS_HISTORY A, TB_BO_MER_MGMT B, TB_VACCT_TRANS C
    WHERE A.I_MID = B.I_MID
      AND A.TXID = C.TXID
      AND A.I_MID = C.I_MID
      AND B.I_MID = C.I_MID
      AND A.PAY_METHOD IN ('02')
      AND A.TRANS_DT BETWEEN '20161016' AND '20161116' 
      AND A.TRANS_DT||A.TRANS_TM BETWEEN '2016101600%3A0000' AND '2016111624%3A0000'     
      AND B.TAX_NO != 'NICEPAY' 
      AND C.SIMULATION_FLG = '0' 
      AND C.STATUS IN ('0', '1', '2', '3', '4') 
) TBL
WHERE RNUM BETWEEN 210000 AND 220000

EXPLAIN PLAN is your friend in all cases. Look for table scans and missing indexes. It's a very complex query with a lot of business logic embedded. — duffymo, Nov 16 '16 at 12:02
Executing a query in a sql tool or executing it with mybatis are quite different things... The query probably is still as fast but mybatis also creates objects and that it slowing things down. So you are basically comparing different things query executing and query executing + object creation. — M. Deinum, Nov 16 '16 at 12:05
sorry.. just executing time. i checked excuting time of query in db server. — Jongho, Nov 16 '16 at 12:23
If `a.trans_dt` is of DATE datatype, then `AND A.TRANS_DT BETWEEN '20161016' AND '20161116'` should really be `AND A.TRANS_DT BETWEEN to_date('20161016', 'yyyymmdd') AND to_date('20161116', 'yyyymmdd')`. Plus, `AND A.TRANS_DT||A.TRANS_TM BETWEEN '2016101600%3A0000' AND '2016111624%3A0000'` would then just be `and a.trans_tm = '%3A0000'` — Boneist, Nov 16 '16 at 12:25
@boniest - That still doesn't explain the difference in times (since all versions have the wrong comparisons); but perhaps if the OP fixes what you pointed out, the difference will be between 1.5 seconds for one and 6 seconds for the other and he/she will be able to move on to something else. :-) — , Nov 16 '16 at 13:10
@mathguy sure, I was merely pointing out problems with the query, not offering a solution - I should have made that clearer. But you're right; not having the implicit conversions/functions on the columns would possibly enable the use of indexes, thus fixing them could possibly aid performance. A shame the OP left out so much information from their question though. — Boneist, Nov 16 '16 at 13:38
@Jongho when you run your query in mybatis, does it use bind variables? Because the query in your question is using literals. There could be performance differences due to one query using binds and the other using literal values, perhaps? Also, you would need to consider data caching. — Boneist, Nov 16 '16 at 13:40
@boniest - My point was, instead of answering the question the OP asked, you answered the question they should have asked, namely - what's the "most wrong" thing in my query and how can I fix it? If they fix that, they won't care (or SHOULDN'T care) that one version takes four times longer than the other - if one is 1.5 seconds and the other 6 seconds, vs. 30 seconds/2 minutes. — , Nov 16 '16 at 13:46
@matghuy I understood what you meant, but I wouldn't like to gamble that your proposed outcome is likely; we don't know enough to speculate. Yes, giving the optimizer a head start by not crippling it right at the start \*might\* help, but if there aren't indexes etc at most it won't harm anything. It will make things easier to maintain in the future, though. — Boneist, Nov 16 '16 at 14:57

blackwizard · Answer 1 · 2016-11-16T16:19:32.040

I have already met such situation: the time difference is not in SQL query execution, but on fetching results.

Oracle JDBC driver default fetch size is 10, that means the ResultSet is fed 10 rows by 10 rows, which make a lot of round-trips to DB when there are 1 million records. The Fetch size must be increased.

With PostgreSql, the default fetch size is unlimited: the ResultSet is fed with the whole result (or until OutOfMemory). The Fetch size could need to be decreased.

To specify the fetch size value in Mybatis:

Use fetchSize attribute in XML select statement:

<select id="listItems" fetchSize="300">SELECT ...</select>

Or use fetchSize option in annotations:

@Select("SELECT ...")
@Options(fetchSize=300)

A value in range 200-500 is often a good compromise.

Same Query, Different Peformance

1 Answers1