Re: BLOB fields slow down queries

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Tue, 26 Sep 2006 23:00:06 GMT
Message-ID: <J682Kr.CAI@igsrsparc2.er.usgs.gov>

Big George wrote:
> Hello,
>
> If I run this query:
>
> select MyID, MyDescription, MyblobField

>>From MyTable

>
> Brings 500 records but it takes 30 seconds !
>
> If I select without any blob fied like:
>
> select MyID, MyDescription

>>From MyTable

>
> It's fast.
>
> The blob fields are storing images like jpg or gif files.
> Approximately, each field stores 250 KB.
> I know that blob fields are slowing down the query, but could be any
> way to accelerate it?
>
> Thank you!
>

It generally is not a good idea to read all of the BLOB's in this fashion. You have 500 records and each BLOB is about 250KB. Doing the math means that your query will return approximately 125MB of data. If you have to do physical reads to satisfy the query, then reading 125MB of data will take some time. You might want to consider putting this table in the KEEP cache so that you only have logical reads, not physical reads. You also might want to consider redefining the SQL so that you return only those BLOB's that you need...maybe you've done this already.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Tue Sep 26 2006 - 18:00:06 CDT