SELECT DISTINCT db_file.ID,db_file.name,db_file.reference,db_file.hash FROM db_file ORDER BY db_file.name;
And the slowliness of the request isn't related to the data filled in the blob, it is only related to the definition of the table.
If I define "file BLOB(2G) NOT NULL" in my table, the request will be slow.
If I define "file BLOB(5M) NOT NULL" in my table, the request will be fast.
If I define "file BLOB(1G) NOT NULL" in my table, the request will also be fast.
Le lundi 09 janvier 2006 à 09:27 -0800, Sunitha Kambhampati a écrit :
Grégoire Dubois wrote: > Hi all, > > Here is the select I do on the following table. If "file" is a > BLOB(2G), the request is very very slow (30-60s), even if there is > only one line for the table. But if I replace BLOB(2G) by BLOB(5M) or > BLOB(1G), the request becomes very fast. > Is there a reason ? If you are retrieving a blob of 2G -1 size, the time taken will be more than if you are retrieving a blob of lets say 5M because you are reading more data in case of the 2G blob from the disk than the 5M blob. But if the data in the file that you insert in the blob(2G) column and the blob(5M) column is the same, then I wouldnt expect the response time to vary. Can you please confirm specifically what size data you are inserting into the blob(2G) and the blob(5M) columns where you are seeing the difference. Thanks, Sunitha. > Is there a workaround ? > > Thank you. > Best regards. > > > > SELECT DISTINCT db_file.ID,db_file.name,db_file.reference,db_file.hash > FROM db_file > ORDER BY db_file.name > > > > CREATE TABLE db_file ( > ID INT GENERATED > ALWAYS AS IDENTITY (START WITH 1,INCREMENT BY 1), > file BLOB ( 2G) NOT > NULL, > name VARCHAR(256) NOT > NULL, > hash VARCHAR( 40) NOT > NULL, > size INT NOT > NULL, > reference VARCHAR( 32) NOT > NULL, > PRIMARY KEY (ID)); > CREATE INDEX db_file_name_index ON db_file (name)" > CREATE UNIQUE INDEX db_file_hash_index ON db_file (hash)" > CREATE INDEX db_file_reference_index ON db_file (reference)" >