[jira] [Created] (SCB-829) An index on TxEvent (globalTxId) should be added

["FuChenGeng (JIRA)" <ji...@apache.org>]

FuChenGeng created SCB-829:
------------------------------

             Summary: An index on TxEvent (globalTxId) should be added
                 Key: SCB-829
                 URL: https://issues.apache.org/jira/browse/SCB-829
             Project: Apache ServiceComb
          Issue Type: Improvement
          Components: Saga
    Affects Versions: saga-0.3.0
         Environment: postgres and mysql
            Reporter: FuChenGeng
             Fix For: saga-0.3.0


When the amount of data in the TxEvent table is large, the select SQL statement of method(

findByEventGlobalTxIdAndEventType

)  is executed very slowly. Here, an index on globalTxId should be added.

 

Here is the SQL statement. it will be executed 3 times in a transaction with 2 subtransactions.

@Query("SELECT DISTINCT new org.apache.servicecomb.saga.alpha.core.TxEvent("
 + "t.serviceName, t.instanceId, t.globalTxId, t.localTxId, t.parentTxId, "
 + "t.type, t.compensationMethod, t.payloads "
 + ") FROM TxEvent t "
 + "WHERE t.globalTxId = ?1 AND t.type = ?2 "
 + " AND ( SELECT MIN(t1.retries) FROM TxEvent t1 "
 + " WHERE t1.globalTxId = t.globalTxId "
 + " AND t1.localTxId = t.localTxId "
 + " AND t1.type IN ('TxStartedEvent', 'SagaStartedEvent') ) = 0 ")

 



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)