Hi all,
When experimenting with BLOB's I ran into a performance issue
that I cannot completely explain, but it could be a bug.
Given the following table:
CREATE TABLE BLOB_TABLE (
BLOB_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1,
INCREMENT BY 1),
BLOB_SIZE BIGINT NOT NULL,
BLOB_CRC BIGINT NOT NULL,
BLOB_NAME VARCHAR(255) NOT NULL,
BLOB_DATA BLOB(2G) NOT NULL,
UNIQUE (BLOB_CRC, BLOB_SIZE),
PRIMARY KEY (BLOB_ID)
);
which is populated with 27 rows,
where the sum of all BLOB sizes is 5,885,060,164 bytes
(about 200 MB average per BLOB, but ranging from 10 MB to 750 MB).
Some queries on this table are executed really
fast (almost instantaneous response).
However, the following query needs about 10 minutes to complete:
SELECT BLOB_ID, BLOB_NAME, BLOB_SIZE, BLOB_CRC FROM BLOB_TABLE;
I reasoned that maybe Derby is scanning the whole table
(including the blob contents) so I tried to add a dummy WHERE
clause (dummy because all BLOB_ID's are greater than 0)
to offer a clue as to what rows (all of course) are needed,
as follows
SELECT BLOB_ID, BLOB_NAME, BLOB_SIZE, BLOB_CRC FROM BLOB_TABLE WHERE BLOB_ID
> 0;
and that helped: instantaneous response.
But I really think that the original query,
without the where clause, should not be any slower.
I am using Derby 10.1.3.1 embedded, Windows XP and Sun Java 1.5.0_06.
Both queries executed with a Statement, not a PreparedStatement.
Kind regards,
Piet Blok
