export transactions csv export slow

[rabbitz]

Exporting transactions to csv is too slow, queries take too long, and the web interface doesn't give any hints.

Related issues:

#41

Related pull requests:

Magickbase/ckb-explorer-frontend#9
nervosnetwork/ckb-explorer#1241

[rabbitz]

Reason

The table only needs to export the capacity corresponding to cell_inputs and cell_outputs corresponding to ckb_transaction, but the display_inputs and display_outputs that are called now will cause additional query of table blocks and udts.

Solution

1. Use limit(5000).find_in_batches(batch_size: 1000) query according to AccountBook according to ckb address
2. Avoid N+1 queries, use ckb_transactions.includes(:inputs, :outputs)
3. The inputs = ckb_transaction.display_inputs method of the ckb_transaction object is rewritten as

https://github.com/nervosnetwork/ckb-explorer/blob/develop/app/controllers/api/v1/address_transactions_controller.rb#L62

inputs =
   if ckb_transaction.is_cellbase
     return [""]
   else
     cell_inputs_for_display = ckb_transaction.inputs.to_a.sort_by(&:id)
     cell_inputs_for_display.map(&:capacity)
   end

4. The outputs = ckb_transaction.display_outputs method of the ckb_transaction object is rewritten as

https://github.com/nervosnetwork/ckb-explorer/blob/develop/app/controllers/api/v1/address_transactions_controller.rb#L63

cell_outputs_for_display = ckb_transaction.outputs.to_a.sort_by(&:id)
outputs = cell_outputs_for_display.map(&:capacity)

Effect

Locally query the staging environment address ckt1qpuljza4azfdsrwjzdpea6442yfqadqhv7yzfu5zknlmtusm45hpuqgp07xsfzppphcyt4n5plw47tgzqv76gvgpqqyu9k6c Export date 2023-05-05 to 2023-06-01 The number of ckb_transactions records is 2173

--> Api::V1::AddressTransactionsController download_csv
       user system total real
   1.151034 0.345807 1.496841 ( 12.128878)

@ShiningRay @Keith-CY @zmcNotafraid

[rabbitz]

Related PR: nervosnetwork/ckb-explorer#1301