If you have more than a million records in the tables sales_flat_order and payone_transaction and payone_protocol_transactionstatus the performance of the transaction processing is decreasing dramatically.
I measured 4-5 seconds per transaction, which in shops with a higher load and many orders, causes a delay in the processing, as there are more transaction status which have to be processed than one cycle, which is limited to 35 seconds per cycle (hardcoded), can do.
The issue can be resolved by adding a index to the following columns:
sales_flat_order.payone_cancel_substitute_increment_id (used in \Payone_Core_Model_Service_TransactionStatus_Execute::_getIncrementId)
payone_transaction.txid (used in \Payone_Core_Model_Service_Transaction_Update::updateByTransactionStatus for loading the transaction)
I fixed the issue by manually adding an index to the db table. Please create an update script for the next version. Maybe adding an index to payone_protocol_transactionstatus.txid is a good idea as well.
BTW: I don't get the idea of 35 and \Payone_Core_Model_Service_TransactionStatus_Execute::MAX_EXECUTION_TIME which has a value of 30 (seconds) which can't be configured in the Magento Backend. It would be a good idea to use the same value and add that option to the Backend Configuration as well. I think it could be easily set to 50 to 55 seconds without any risk.
With this changes, I could reduce the time for one transaction from 4-5 seconds to 0.25-0.35 seconds per transaction.
fjbender
commented
4 years ago
If you have more than a million records in the tables sales_flat_order and payone_transaction and payone_protocol_transactionstatus the performance of the transaction processing is decreasing dramatically.
I measured 4-5 seconds per transaction, which in shops with a higher load and many orders, causes a delay in the processing, as there are more transaction status which have to be processed than one cycle, which is limited to 35 seconds per cycle (hardcoded), can do.
The issue can be resolved by adding a index to the following columns: sales_flat_order.payone_cancel_substitute_increment_id (used in \Payone_Core_Model_Service_TransactionStatus_Execute::_getIncrementId) payone_transaction.txid (used in \Payone_Core_Model_Service_Transaction_Update::updateByTransactionStatus for loading the transaction)
I fixed the issue by manually adding an index to the db table. Please create an update script for the next version. Maybe adding an index to payone_protocol_transactionstatus.txid is a good idea as well.
BTW: I don't get the idea of35 and \Payone_Core_Model_Service_TransactionStatus_Execute::MAX_EXECUTION_TIME which has a value of 30 (seconds) which can't be configured in the Magento Backend. It would be a good idea to use the same value and add that option to the Backend Configuration as well. I think it could be easily set to 50 to 55 seconds without any risk.
With this changes, I could reduce the time for one transaction from 4-5 seconds to 0.25-0.35 seconds per transaction.