I am running below SQL query via Sqlline.sh, and I think it is running too
slow (57s). Could someone confirm whether this response time is normal, or I
am doing something wrong?
Here is the query:
0: jdbc:ignite:thin://127.0.0.1/> SELECT DISTINCT ACCOUNT_ID FROM
PERF_POSITIONS;
+--------------------------------+
| ACCOUNT_ID |
+--------------------------------+
| 1684 |
| 1201 |
| 1686 |
...
...
| 1441 |
+--------------------------------+
1,001 rows selected (57.453 seconds)
My setup is a single Azure VM (CentOS 7) with 16 Cores and 64GB RAM. The
host is idle other than the Ignite process.
My Dataset has 50 million rows with a total of 1001 distinct ACCOUNT_ID
values. Rows are almost evenly distributed among the account_id's. As you
can see in the below table definition, ACCOUNT_ID is first column of the
primary key and the index.
CREATE TABLE PERF_POSITIONS (
ACCOUNT_ID VARCHAR(50) NOT NULL,
EFFECTIVE_DATE DATE NOT NULL,
FREQUENCY CHAR(1) NOT NULL,
SOURCE_ID INTEGER NOT NULL,
SECURITY_ALIAS BIGINT NOT NULL,
POSITION_TYPE VARCHAR(10),
IT VARCHAR(50),
IN VARCHAR(255),
PAI VARCHAR(100),
TIC VARCHAR(100),
GR DOUBLE,
NR DOUBLE,
GRL DOUBLE,
IR DOUBLE,
ABAL DOUBLE,
BG DOUBLE,
EG DOUBLE,
CGD DOUBLE,
CGC DOUBLE,
CFG DOUBLE,
GLG DOUBLE,
IB DOUBLE,
WT DOUBLE,
BL DOUBLE,
EL DOUBLE,
CDL DOUBLE,
CCL DOUBLE,
CL DOUBLE,
GLL DOUBLE,
IBL DOUBLE,
NC DOUBLE,
BP DOUBLE,
EP DOUBLE,
CP DOUBLE,
PR DOUBLE,
BAI DOUBLE,
EAI DOUBLE,
CI DOUBLE,
SF VARCHAR(10),
US VARCHAR(255),
UD DATE,
PRIMARY KEY (ACCOUNT_ID, EFFECTIVE_DATE, FREQUENCY, SOURCE_ID,
SECURITY_ALIAS, POSITION_TYPE)
)
WITH "template=partitioned, backups=1, affinityKey=ACCOUNT_ID,
KEY_TYPE=ie.models.PerfPositionKey, VALUE_TYPE=ie.models.PerfPosition";
CREATE INDEX PERF_POSITIONS_IDX ON PERF_POSITIONS (ACCOUNT_ID,
EFFECTIVE_DATE, FREQUENCY, SOURCE_ID, SECURITY_ALIAS, POSITION_TYPE);
When I run the query, I see below warning showing up in the log.
[22:32:51,598][WARNING][client-connector-#136][IgniteH2Indexing] Query
execution is too long [time=57260 ms, sql='SELECT DISTINCT
__Z0.ACCOUNT_ID __C0_0
FROM PUBLIC.PERF_POSITIONS __Z0', plan=
SELECT DISTINCT
__Z0.ACCOUNT_ID AS __C0_0
FROM PUBLIC.PERF_POSITIONS __Z0
/* PUBLIC."_key_PK" */
, parameters=[]]
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
