db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Hohl, Gerrit" <g.h...@aurenz.de>
Subject AW: Paging performance problem
Date Mon, 04 Jan 2021 15:08:25 GMT
Hello everyone,

I guess I found at least one solution:

CREATE INDEX data_create_timestamp_id_key ON data (create_timestamp ASC, id ASC)

SELECT d.* FROM (SELECT id FROM data ORDER BY create_timestamp OFFSET 4499990 ROWS FETCH NEXT
10 ROWS ONLY) as tmp, data d
WHERE (tmp.id = d.id)

Takes less than 4 seconds with my test table.
But I'm wondering if that is really the best solution, especially as I'm still not sure about
the reason.

Isn't that new index I created also an unbalanced binary tree? Shouldn't it take the same
amount of time?
But if I drop it and have only the primary key and data_create_timestamp_key index, the query
takes even longer than my originally one (100s ).

In addition: I passed a simply SQL query. Wondering if I also can recreate that with JPA/JPQL...

Regards,
Gerrit

Von: Hohl, Gerrit
Gesendet: Montag, 4. Januar 2021 15:43
An: Derby Discussion <derby-user@db.apache.org>
Betreff: Paging performance problem

Hello everyone,

we're trying to use OFFSET and LIMIT for paging. But as our tables our growing, we're running
in some performance problems we didn't expect.

Let's say we have the following structure (the table can also have more columns, but for the
sake of simplicity...):

CREATE TABLE data (id INTEGER NOT NULL, create_timestamp TIMESTAMP NOT NULL, PRIMARY(id));
CREATE INDEX data_create_timestamp_key ON data (create_timestamp ASC);

We have around 5m records in it. And now we do the two following queries:

SELECT * FROM data ORDER BY create_timestamp OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
SELECT * FROM data ORDER BY create_timestamp OFFSET 4499990 ROWS FETCH NEXT 10 ROWS ONLY;

While the first query returns right after it was started, the 2nd query takes almost a minute
to complete.
First we thought that Derby might not have used the index for some reasons.
But the runtime statistics showed that it is using it.

We assume that it is due to the fact that the index might be some kind of unbalanced binary
tree.
Is that true? And is there any work-around?

Gruß
Gerrit

P.S.: We still use Apache Derby 10.14.2.0 as we're still tight to Java 8.

Mime
View raw message