SpringBoot multi Thread. Query extremely slow

[小林裕幸]

"I'm using Spring Boot with a WebSocket handler to UPSERT a large number of requests into an H2 database. I was able to update the data successfully with up to 150 threads. In this environment, I'm using Spring's scheduled task feature to print the state of a table using an aggregation function every 5 seconds. However, the query for this aggregation function becomes extremely slow. I may have to wait for several tens of minutes.

The query I'm using is as follows:

SELECT A.TOPIC, (A.RANK + B.RANK + C.RANK + D.RANK + E.RANK + F.RANK) AS SCORE FROM
(select TOPIC,VAL, rank() over(order by VAL desc) AS rank from (SELECT TOPIC,SUM(VAL) AS VAL FROM RSS_DATA WHERE KEY1='R' AND VAL!='NaN' GROUP BY TOPIC,KEY1) ) A
INNER JOIN (SELECT TOPIC,VAL,rank() over(order by VAL desc) AS rank from RSS_DATA WHERE KEY1='U' AND VAL!='NaN') B ON A.TOPIC=B.TOPIC
INNER JOIN (SELECT TOPIC,VAL,rank() over(order by VAL desc) AS rank from RSS_DATA WHERE KEY1='A' AND VAL!='NaN') C ON A.TOPIC=C.TOPIC
INNER JOIN (SELECT TOPIC,VAL,rank() over(order by VAL asc) AS rank from RSS_DATA WHERE KEY1='IR' AND VAL!='NaN') D ON A.TOPIC=D.TOPIC
INNER JOIN (SELECT TOPIC,VAL,rank() over(order by VAL desc) AS rank from RSS_DATA WHERE KEY1='IA' AND VAL!='NaN') E ON A.TOPIC=E.TOPIC
INNER JOIN (SELECT TOPIC,VAL,rank() over(order by VAL desc) AS rank from RSS_DATA WHERE KEY1='V' AND VAL!='NaN') F ON A.TOPIC=F.TOPIC
ORDER BY (A.RANK + B.RANK + C.RANK + D.RANK + E.RANK + F.RANK) ASC;

The version is 2.1.214."

Things I have checked:

No entries in the LOCK table.
No depletion in the connection pool.
The SESSIONS table: all queries except this one disappear instantly.
No heap depletion.
Things I have tried:

LOCKMODE=0, but it didn't make a difference.
LOCK_TIMEOUT set to 100ms, but it didn't make a difference.

thanks

[Andreas Reichel]

Greetings.

Without knowing the Table definition and the indexes, nobody will be able to help.

However, you may look for:

1) the JOINERS are all repetitive, filtering for a particular value of "key1". Instead repeating for each value, just use "key1" for the aggregation.

2) ensure, that "key1" and "val" are indexed and that those indices are used. Beware losing the index access when introducing the sub-queries. Depending on your data volume, it may be more efficient, to write the sub-queries into a temporary table, then index that and run the main query on the indexed temporary table. 

3) avoid the "val != 'NaN'" as it will likely result in a full table scan. Instead, write something like " val IN (...)" which can used indexes (at least with latest Git Snapshot, if I understood latest commits correctly)

4) run you query against H2 directly and eliminate the Spring/Hibernate Layer. Get a proper execution plan and check the reads and used indices.

Avoid Full table Scans at all cost.

Good Luck!

Andreas

[Andreas Reichel]

Greetings once again.

One thing you could do for confirming the performance: Export your tables into PARQUET files and load those into a Column-based database (e.g. duckdb), which may have an advantage for your particular aggregation. Then run your query against that Column-based DB.

IF the performance is the same (more or less), then the issue is with your table/index design or with Spring/Hibernate. (I assume this is the case here.)

Only if the performance was much better, then this may be a H2 issue worth to report.

Good luck

Andreas