db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Piet Blok" <pbh...@wanadoo.nl>
Subject Re: Query on a blob table
Date Tue, 24 Jul 2007 06:50:06 GMT
Raymond,

Some time ago I encountered the same problem and reported it here in a thread "Strange performance
issue with BLOB's". I also found a workaround for this problem:

If this is your original query: 
select sum(CONTENT_SIZE) from DOCUMENT_VERSION;

add a dummy WHERE clause like this:
select sum(CONTENT_SIZE) from DOCUMENT_VERSION where DOCUMENT_ID > 0;

(assuming ofcourse that DOCUMENT_ID is a positive number)



Kind regards,

Piet Blok

  ----- Original Message ----- 
  From: Raymond Kroeker 
  To: derby-user@db.apache.org 
  Sent: Tuesday, July 24, 2007 3:20 AM
  Subject: Query on a blob table


  Hi All,
      I have a table encompassing blobs as such:

  CREATE TABLE DOCUMENT_VERSION
  (
     DOCUMENT_ID BIGINT not null,
     CONTENT BLOB not null,
     CONTENT_SIZE BIGINT not null,
     CONTENT_CHECKSUM VARCHAR(256) not null, 
     CHECKSUM_ALGORITHM VARCHAR(16) not null,
     CONSTRAINT DOCUMENT_VERSION_PK PRIMARY KEY (DOCUMENT_ID)
  );


    Now I've loaded 175 rows of 256B blobs and a single row containing a 250MB blob.  When
I run the query 

  select sum(CONTENT_SIZE) from DOCUMENT_VERSION;

    it takes ~10s += 1.5s to complete.  Am I missing something?

    I've tried indexing CONTENT_SIZE (after seeding the data) without any luck.

  -- 
  --------------------------------------------------------------------------------
  Raymond Kroeker
  thinkParity Solutions Inc. 
Mime
View raw message