You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Grégoire Dubois <gr...@online.fr> on 2006/01/09 13:42:17 UTC
SELECT very slow when BLOB(2G)
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 ? 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)"
Re: SELECT very slow when BLOB(2G)
Posted by Grégoire Dubois <gr...@online.fr>.
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)"
> > >
> >
> >
Re: SELECT very slow when BLOB(2G)
Posted by Grégoire Dubois <gr...@online.fr>.
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)"
> >
>
>
Re: SELECT very slow when BLOB(2G)
Posted by Sunitha Kambhampati <ks...@gmail.com>.
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)"
>