"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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/17b328fc-483e-4538-9e19-45a17002f2aen%40googlegroups.com.
