Didn't find a clear hint for this in the docs... When is a BLOB actually read from the database?
I made some tests (admitting I am not a DB expert) but it seems that a BLOB is only read from disk when it's really needed.
A unique FIND on the record that contains the BLOB (54 MB) causes only 1 DB read.
A LENGTH (BLOB field) causes no DB reads.
COPY-LOB of the LOB field causes a bunch of DB reads (13.000).
I ran those tests in a DB that did only contain a single DB table with only a single record.
[quote user="Mike Fechner"]admitting I am not a DB expert
[/quote]
Ah so now you admit it?! ;)
I remember reading this exact fact earlier in the month Mike, so the LOB isn't actually read unless it's needed.
Thanks James!
the record contains a "lob locator" value that has the length and the location where the lob data are stored.
as you see, the lob data are not retrieved when the record is but only when needed.
deleting the record will cause the lob data to be deleted also.
updating the other fields in the record will not cuase the lob data to be touched.
note that creation, deletion, and updates of lob data are logged in the transaction logs. this can sometimes generate a lot of stuff in bi and ai logs.
regards,
gus (gus@progress.com)
"Debugging is twice as hard as writing the code in the first place.
Therefore, if you write the code as cleverly as possible, you are,
by definition, not smart enough to debug it." -- Brian Kernighan
> On Nov 23, 2015, at 8:46 AM, Mike Fechner wrote:
>
> Update from Progress Community [https://community.progress.com/]
>
> Mike Fechner [https://community.progress.com/members/mikefechner]
>
> Didn't find a clear hint for this in the docs... When is a BLOB actually read from the database?
>
> I made some tests (admitting I am not a DB expert) but it seems that a BLOB is only read from disk when it's really needed.
>
> A unique FIND on the record that contains the BLOB (54 MB) causes only 1 DB read.
>
> A LENGTH (BLOB field) causes no DB reads.
>
> COPY-LOB of the LOB field causes a bunch of DB reads (13.000).
>
> I ran those tests in a DB that did only contain a single DB table with only a single record.
>
> View online [https://community.progress.com/community_groups/openedge_development/f/19/t/21546]
>
> You received this notification because you subscribed to the forum. To stop receiving updates from only this thread, go here [https://community.progress.com/community_groups/openedge_development/f/19/t/21546/mute].
>
> Flag [https://community.progress.com/community_groups/openedge_development/f/19/t/21546?AbuseContentId=55655ec3-bd9d-4ba0-90e2-65198ebf4aca&AbuseContentTypeId=46448885-d0e6-4133-bbfb-f0cd7b0fd6f7&AbuseFlag=true] this post as spam/abuse.
[quote user="gus"]note that creation, deletion, and updates of lob data are logged in the transaction logs. this can sometimes generate a lot of stuff in bi and ai logs.[/quote]
This is an important point. We recently added compression to our BLOBs to try and reduce wasted space. 200GB of BLOBs were therefore updated in the DB. The AI Files went rather crazy.
Thanks, Gus!