db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <g.h...@aurenz.de>
Subject AW: Derby Scheduler and FETCH FIRST question
Date Wed, 24 Jan 2018 15:39:23 GMT
Hello everyone,

 

tried something different. I thought I could fool the scheduler.

First maybe I should add that the "timestamp" column is a VARCHAR.

But it shouldn't make much difference as it has an index, right?

 

SELECT t2.timestamp FROM history t2 ORDER BY t2.timestamp DESC FETCH
FIRST 10 ROWS ONLY

That took 3 seconds and returned just the timestamps. Okay, maybe - also
the column has an index - there are really many rows so it takes some
time.

But to be honest: I thought it would run in a blink of an eye.

 

Now I tried to inject that in the original query as sub-select, but I
only select the timestamp as field for the result:

SELECT t1.timestamp FROM history t1 WHERE (t1.timestamp IN (

SELECT t2.timestamp FROM history t2 ORDER BY t2.timestamp DESC FETCH
FIRST 10 ROWS ONLY

))

That took 11 seconds - more than tripled, also - as I already mentioned
- that column has an index.

And this time I even filter for it.

 

If I query for the whole row instead of just one column it even gets
worse:

SELECT t1.* FROM history t1 WHERE (t1.timestamp IN (

SELECT t2.timestamp FROM history t2 ORDER BY t2.timestamp DESC FETCH
FIRST 10 ROWS ONLY

))

It takes 3 minutes. Maybe one would say that it is maybe the fault of
the size of the Blob field. So it takes some time to transfer the
selected rows. But the maximum size of the Blob field is 7 KB, the
minimum 64 Bytes. So for 10 rows you have 70KB - which is almost
nothing.

 

Would be great if someone could shed some light on this matter.

 

Regards,

Gerrit

 

Von: Hohl, Gerrit 
Gesendet: Mittwoch, 24. Januar 2018 13:46
An: 'Derby Discussion'
Betreff: Derby Scheduler and FETCH FIRST question

 

Hello everyone,

 

I'm using Apache Derby v10.14.1.0 and having some problems using the
FETCH FIRST clauses.

https://db.apache.org/derby/docs/10.14/ref/rrefsqljoffsetfetch.html

I'm accessing the database using the Derby Embedded driver.

 

I have a table which contains some indexes as well as some fields and a
BLOB field. The table is somewhat big (means many rows, ~13 GB).

I'm using a query like this (timestamp has an index):

 

SELECT * FROM history ORDER BY timestamp DESC FETCH FIRST 10 ROWS ONLY

 

The query takes ages (about 27 minutes for that ~13 GB table) and I can
see how Derby slowly fills up my harddisk.

And a look in the "tmp" folder of the database shows several ".tmp"
files.

First I get several files having 10 MB, then I get two big files having
5 GB, then the 10 MB files are deleted, then the 5 GB files are deleted
and finally I get the result.

As I thought something is wrong with my application I also did the same
query on the same database and table using SQuirreL v3.8.1. But the
result is the same.

 

I would have expected that the scheduler of Derby would first look at
the timestamp column / index (which should be sorted), taking the first
10 values from there and

finally reading the first 10 rows matching these values.

Instead it seems that it first processes the " SELECT * FROM history"
part (as memory is not sufficient it swaps it to the harddisk), orders
it and takes the first 10 elements.

 

Is that correct?

And if that is correct, where is the benefit of FETCH FIRST - beside
that maybe not that much data is transferred (maybe only interesting if
you use Derby not by the Embedded Driver because of the TCP/IP
connection)?

 

 

Regards,

Gerrit

 


Mime
View raw message