"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 -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/17b328fc-483e-4538-9e19-45a17002f2aen%40googlegroups.com.
