[
https://issues.apache.org/jira/browse/IGNITE-21275?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17808559#comment-17808559
]
Konstantin Orlov commented on IGNITE-21275:
-------------------------------------------
One of the major contributor to the difference in performance is the lack of a
partition pruning (PP) and partition awareness (PA) in SQL. With both PP and PA
I would expect to get ~2x increase of performance in SQL.
Another suspicious thing (I'm not 100% sure here) is a performance of table
scan. On my laptop, average time of "embedded kv get" on a single node single
partition cluster is 35.249 us, while InternalTable#scan (the method we are
using in SQL) by primary key index takes ~50us (I measured time from invoking
"request" on subscription till invoking "onComplete" on subscriber).
The rest is spread across an entire query execution pipeline (still need to be
investigated, but it will look more like polishing though).
> Up to 5x difference in performance between SQL API and key-value API
> --------------------------------------------------------------------
>
> Key: IGNITE-21275
> URL: https://issues.apache.org/jira/browse/IGNITE-21275
> Project: Ignite
> Issue Type: Improvement
> Components: sql
> Reporter: Ivan Artiukhov
> Priority: Major
> Labels: ignite-3
> Attachments: 1240-sql-insert.png, 1240-sql-select.png,
> 1241-jdbc-insert.png, 1241-jdbc-select.png, 1242-kv-get.png, 1242-kv-put.png
>
>
> h1. Build under test
> AI3 rev. ca21384f85e8c779258cb3b21f54b6c30a7071e4 (Jan 16 2024)
> h1. Setup
> A single-node Ignite 3 cluster with default config.
> h1. Benchmark
> Compare two benchmark runs:
> * a benchmark which uses KeyValueView to perform single {{put()}} and
> {{{}get(){}}}:
> [https://github.com/gridgain/YCSB/blob/ycsb-2023.11/ignite3/src/main/java/site/ycsb/db/ignite3/IgniteClient.java]
>
> * a benchmark which performs {{INSERT}} and {{SELECT}} via {{Statement}}
> objects by using Ignite SQL API:
> [https://github.com/gridgain/YCSB/blob/ycsb-2023.11/ignite3/src/main/java/site/ycsb/db/ignite3/IgniteSqlClient.java]
>
> h1. Run 1, PUT/INSERT
> Insert N unique entries into a single-node cluster from a single-threaded
> client.
> h2. KeyValueView
> N = 250000
> {code:java}
> Command line: -db site.ycsb.db.ignite3.IgniteClient -load -P
> /opt/pubagent/poc/config/ycsb/workloads/workloadc -threads 1 -p
> recordcount=250000 -p warmupops=50000 -p dataintegrity=true -p
> measurementtype=timeseries -p status.interval=1 -p hosts=192.168.1.37 -s
> {code}
> !1242-kv-put.png!
> h2. SQL API
> N = 15000
>
> {code:java}
> Command line: -db site.ycsb.db.ignite3.IgniteSqlClient -load -P
> /opt/pubagent/poc/config/ycsb/workloads/workloadc -threads 1 -p
> recordcount=150000 -p warmupops=15000 -p dataintegrity=true -p
> measurementtype=timeseries -p status.interval=1 -p hosts=192.168.1.47 -s
> {code}
> !1240-sql-insert.png!
>
> h1. Run 2, GET/SELECT
> Get N entries inserted on Run 1.
> h2. KeyValueView
> N = 250000
>
> {code:java}
> Command line: -db site.ycsb.db.ignite3.IgniteClient -t -P
> /opt/pubagent/poc/config/ycsb/workloads/workloadc -threads 1 -p
> operationcount=250000 -p recordcount=250000 -p warmupops=50000 -p
> dataintegrity=true -p measurementtype=timeseries -p status.interval=1 -p
> hosts=192.168.1.37 -s{code}
> !1242-kv-get.png!
>
> h2. SQL API
> N = 150000
> {code:java}
> Command line: -db site.ycsb.db.ignite3.IgniteSqlClient -t -P
> /opt/pubagent/poc/config/ycsb/workloads/workloadc -threads 1 -p
> operationcount=150000 -p recordcount=150000 -p warmupops=15000 -p
> dataintegrity=true -p measurementtype=timeseries -p status.interval=1 -p
> hosts=192.168.1.47 -s {code}
> !1240-sql-select.png!
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
