Ok, I must have a problem, as I seem to have the same problem with mysql.
Thanks for your help.
Best regards.

Le lundi 09 janvier 2006 à 20:02 +0100, Grégoire Dubois a écrit :
I don't read the blob in my request (the blob is "db_file.file", and it isn't used in the select) :
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)"
>