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 Tue, 10 Jan 2006 02:10:20 GMT
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)"
> > >
> > 
> > 

Mime
View raw message