Kbase P88663: Best practices with BLOB/CLOB fields
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/2/2011 |
|
Status: Verified
GOAL:
Best practices with BLOB/CLOB fields
GOAL:
Is a dedicated area required for BLOB/CLOB fields?
GOAL:
Should BLOB/CLOB fields have their own dedicated area?
GOAL:
Improving performance when using BLOBs/CLOBs
GOAL:
What record per block setting for Lob objects?
GOAL:
Why LOBS should be placed in Type II Storage Areas
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.x
FIX:
The BLOB and CLOB Field Attributes box in the Data Dictionary specifies in which area these database objects will be located. They can be located in areas different to where the rest of the fields for the table are stored, but don't have to be. The decision to place them in a different area should be based on the same criteria as when deciding whether or not to locate any other database object (e.g. indexes) in its own area, i.e. it should be done for reasons of space, and for balancing I/O and reducing I/O contention.
The advantage of storing BLOBs/CLOBs are in a separate Storage Area, is that different Records per Block values can be used than those for the associated table and in addition, Type II storage area architecuture can be used (since OpenEdge 10+) where the number of Blocks per Cluster can also be defined for the Lob-Storage-Area. LOBs in type II areas share the same benefit as other objects - defining clusters ensures that contiguous space is created for storing the objects, thus eliminating some amount of search time when writing or retrieving the data.
In Type II Storage Areas, possible values of blocks per cluster are: 8, 64, or 512 - the value chosen will depend on the database blocksize and the BLOB/CLOB size. Suppose, for example, the average BLOB is 10MB is a database with 4KB blocksize: this means that the object will be stored in around 10240/4 = 2560 blocks in a Type I Storage Area architecture. With a blocks per cluster of 512 in a Type II Storage Area architecture, this object would populate 5 clusters.
Lobs are by nature, 'large' so they're cut into slices and each slice is stored as a record fragment. So the LOB is saved in (almost) the same format as regular record. It's not block based. Consider a rare case where there are small size lob fields less than the database blocksize, the storage engine will then store multiple lobs within a block, just like small records are stored depending on the Records per Block (rpb) configuration.
There is a strong argument in defining 1 rpb for Lob areas (Type I or Type II) especially where the lob objects get updated/appended to. Lobs by nature are 'large' so the argument is: if i'm wasting (max 8KB database blocksize) per lob, that's a small compromise if i'm maximising the addressable space in the database block itself and in the area (in the case of Type I areas). Remember in the Type I Storage Area architecture, a 1 rpb gives the maximum addressable space in the storage area:
Type I: 1rpb 512 GB for 8KB
Type I: 1rpb 256 GB for 4KB
As with general recommendations, the particular configurations for each application environment will be particular to that environment. If the client creating the LOB is an ODBC client, then please refer to Solution P183457