Each call to query() takes at least 13ms #391
Description
(you don't have to strictly follow this form)
Describe the situation
What exactly works slower than expected?
the call to query() is taking at least 13ms just to run any query, even if the query is the simplest and requires no data at all. This prevents us from using chdb in an environment where it could have been used as a replacement for a real clickhouse, like a testing environment.
How to reproduce
- Which ClickHouse server version to use
chdb-node 1.3.0 - Which interface to use, if matters
chdb-node Non-default settings, if anyCREATE TABLEstatements for all tables involved- Sample data for all these tables, use clickhouse-obfuscator if necessary
No data necessary for testing. - Queries to run that lead to slow performance
All select statements:
SELECT * FROM numbers(1)
Expected performance
What are your performance expectation, why do you think they are realistic? Has it been working faster in older ClickHouse releases? Is it working faster in some specific other system?
I expect a call to SELECT * FROM numbers(1) to be sub 1ms, as it's the simplest query possible. However, it takes 13ms. I've tested with various queries (see my js script below), the baseline time it takes is 13ms.
This prevents us from using chdb as a local db for testing environments, where it could be a perfect fit.
Additional context
Add any other context about the problem here.
const database = 'local';
const tableSchema = `
CREATE TABLE IF NOT EXISTS ${database}.chat_events
(
timestamp DateTime64,
tenant_id Int64,
conversation_id Int64,
event_type String,
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(timestamp)
ORDER BY (tenant_id, toStartOfDay(timestamp), timestamp)
PRIMARY KEY (tenant_id, toStartOfDay(timestamp));
`;
async function run() {
let time = performance.now();
const chdb = await import('chdb');
// took 100ms
console.log(`Importing chdb took ${Math.trunc(performance.now() - time)}ms`);
const {Session} = chdb;
const sess = new Session();
time = performance.now();
sess.query(`CREATE DATABASE IF NOT EXISTS ${database}`);
// 42ms
console.log(`Creating database took ${Math.trunc(performance.now() - time)}ms`);
time = performance.now();
sess.query(tableSchema);
// 19ms
console.log(`Creating table took ${Math.trunc(performance.now() - time)}ms`);
const rows: Array<any> = [];
for (let i = 0; i < 10000; i++) {
rows.push({
timestamp: (new Date()).getTime(),
tenant_id: 1,
conversation_id: i,
event_type: 'message',
});
}
time = performance.now();
sess.query(`INSERT INTO ${database}.chat_events FORMAT JSONEachRow ${JSON.stringify(rows)}`);
// 30ms
console.log(`Inserting rows took ${Math.trunc(performance.now() - time)}ms`);
time = performance.now();
sess.query('SELECT * FROM numbers(1)');
// 15ms
console.log(`SELECT * FROM numbers(1) session query took ${Math.trunc(performance.now() - time)}ms`);
time = performance.now();
// 13ms
chdb.query('SELECT * FROM numbers(1)');
console.log(`SELECT * FROM numbers(1) query took ${Math.trunc(performance.now() - time)}ms`);
}
run();
the output:
$ npm run ts-node minimal-test.ts
Importing chdb took 100ms
Creating database took 42ms
Creating table took 19ms
Inserting rows took 30ms
SELECT * FROM numbers(1) session query took 15ms
SELECT * FROM numbers(1) query took 13ms
Note: this is a duplicate of chdb-io/chdb-node#29 because I didn't get any response there.