db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Grégoire Dubois <gregoire.dub...@online.fr>
Subject Re: SELECT very slow when BLOB(2G)
Date Mon, 09 Jan 2006 19:02:29 GMT
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)"
> >
> 
> 

Mime
View raw message