Hi Rick,
good to know. 😉
Regards
Gerrit
-----Ursprüngliche Nachricht-----
Von: Rick Hillegas <rick.hille...@gmail.com>
Gesendet: Dienstag, 5. Januar 2021 20:00
An: Derby Discussion <derby-user@db.apache.org>; Hohl, Gerrit <g.h...@aurenz.de>
Betreff: Re: AW: AW: Paging performance problem
Hi Gerrit,
I'm glad that you found a satisfactory answer to your performance question. For
the record, I can find my away around query plans printed in German.
Cheers,
-Rick
On 1/5/21 12:31 AM, Hohl, Gerrit wrote:
> Hi Rick,
>
>
> unfortunately I had to realize that the output of the query plans are printed
> by Derby in German.
> And I'm not quiet sure how to change that.
>
> But we maybe found the reason: The slower version uses the "HEAP" (based on
> the runtime statistics) to get the result while the faster version uses only
> the index ("projection").
>
> The reason behind it seems that the slower version loads records from the
> table (I guess that is what HEAP means - loading records into memory).
> It gets the create_timestamp from the index, but any other column - even if
> it is only the "id" - from the table.
> It even doesn't matter much - at least in the described case - if we only
> query for the "id" or also for the other columns.
>
> The faster version uses a "projection" and works only on the index, no access
> on the table needed.
> If we put the faster version into JOIN like I described in my mail earlier we
> then load only the needed records from the table.
>
> I hope I could explain the magic behind it a little bit, so helps others who
> may come across similar issues.
>
>
> Best regards,
> Gerrit
>
> -----Ursprüngliche Nachricht-----
> Von: Rick Hillegas <rick.hille...@gmail.com>
> Gesendet: Montag, 4. Januar 2021 16:43
> An: Derby Discussion <derby-user@db.apache.org>; Hohl, Gerrit
> <g.h...@aurenz.de>
> Betreff: Re: AW: Paging performance problem
>
> Hi Gerrit,
>
> It's hard to say without seeing the query plans for these scenarios.
> What query plans do you see when you follow the instructions in the "Working
> with RunTimeStatistics" section of the Derby Tuning Guide:
> http://db.apache.org/derby/docs/10.15/tuning/ctundepth13055.html
>
> -Rick
>
> On 1/4/21 7:08 AM, Hohl, Gerrit wrote:
>> 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.
>>
