WhoSoup
commented
4 years ago I tried to use joins vs the current syntax
The current syntax is just a shorthand for an inner join.
Also, the queries seem fast to me. Can you give me an example of a query that takes a long time to execute?
I tried the following (the addresses are taken from the rich list):
curl --data-binary "{\"jsonrpc\": \"2.0\", \"id\": 0, \"method\": \"get-transactions\", \"params\": {\"address\": \"FA2aQnwN6p6RgaLBJYC5FvgMwEg1bMXXgVdUv81SWxE87dqz64A4\", \"asset\":\"PEG\", \"desc\": true}}" -H "content-type:text/plain;" http://localhost:8070/v2
Time taken: 24.0217ms
curl --data-binary "{\"jsonrpc\": \"2.0\", \"id\": 0, \"method\": \"get-transactions\", \"params\": {\"address\": \"FA2aQnwN6p6RgaLBJYC5FvgMwEg1bMXXgVdUv81SWxE87dqz64A4\", \"asset\":\"PEG\", \"desc\": true, \"offset\": 500}}" -H "content-type:text/plain;" http://localhost:8070/v2
Time taken: 26.0235ms
curl --data-binary "{\"jsonrpc\": \"2.0\", \"id\": 0, \"method\": \"get-transactions\", \"params\": {\"height\": 222080}}" -H "content-type:text/plain;" http://localhost:8070/v2
Time taken: 1.0011ms
curl --data-binary "{\"jsonrpc\": \"2.0\", \"id\": 0, \"method\": \"get-transactions\", \"params\": {\"entryhash\": \"a33d4f334a2658c17d3f44158af75f1c32cc6b2f3de9ddc337064c93043d8db0\"}}" -H "content-type:text/plain;" http://localhost:8070/v2
Time taken: 1.0016ms
This is the longest query I could find:
curl --data-binary "{\"jsonrpc\": \"2.0\", \"id\": 0, \"method\": \"get-transactions\", \"params\": {\"address\": \"FA35VhUNxoyK5jH9N2H5MjX3TqCNmp1LwzMNCi3air9RnBLRxZ7i\", \"asset\":\"PEG\", \"desc\": true}}" -H "content-type:text/plain;" http://localhost:8070/v2
Time taken: 72.5855ms (subsequent executions of the same query took 15ms)
The get transactions query is very slow, on some addresses over 20s when also filtering by asset. This query is just very inefficient.
I tried to use joins vs the current syntax, and got the execution fast, but the number of rows affected the time it took to fetch. If you were to query 1 row, I could get the query to run in ~150ms. But when you ask for 25 rows, the query time was at 3s, and 50 pushes it close to 6s.
I don't have that much information as to what is making things so slow, so some investigation is needed. I have noticed that things are slower with more tables. We currently make batches and transactions different tables, but it might be better to make it 1 table. The duplication of data wouldn't be that much given most txs are a batch of 1. If it improves our query time, the duplicated data is worth the cost.