User Guide

10.1
- 10.5 HotFix 2
- 10.5 HotFix 1
- 10.5
- 10.2 HotFix 1
- 10.2
- 10.0 HotFix 1
- 10.0

Back Next

Reducing Database I/O

This section provides information on reducing database input and output operation.

IDX Size

The physical size of the IDX will determine how efficiently the database cache will operate. Reducing the size of the IDXs will improve performance. This is achieved by selecting the most appropriate Compressed-Key-Data value as described in the

Compressed Key Data

section below and using flattening to reduce the number of rows (Refer to the

Flattening IDTs

section for more information).

Compressed Key Data

The IDX stores fuzzy keys and identity data for matching. The identity data is compressed and stored using an algorithm selected with the IDX-definition’s

Compress-Method

parameter. All methods will compress the identity data and store it in the IDX together with its fuzzy key.

If the length of the IDX record exceeds the DBMS’s

maximum segment length

(256 bytes) DCE can either,

Method 0

split the IDX record into multiple adjacent segments (which are all shorter than the length limit).

Method 1

truncate the IDX record at the length limit and only store one segment. This forces additional I/O at run time if the IDX record is selected for matching, as the matching data must be read from the IDT record.

IDX segments are fixed in length and have the following layout:

Partitions Fuzzy

SSA-NAME3 Key

Compressed Identity Data

The

segment length

is the sum of

partition length (optional, user defined)

SSA key length (5 or 8 bytes)

Compress-Key-Data(n)

parameter

4 bytes of additional overhead

Since the segment length is fixed, choosing an appropriate value for

is important because it affects the total amount of space used and the I/O performance of the index. Determining an optimal value for

requires knowledge of the characteristics of the source data and how well it can be compressed. If

is set too high, all segments will use more space than necessary. If

is too low, records will be split into multiple segments, incurring extra overhead for the duplication of the Partition and Fuzzy Key in each segment.

Measuring Compression

The Table Loader can be used sample the source data and produce a histogram of the Compressed Identity Data lengths. A sample table appears below:


loadit>    Histogram:                               KG KeyLen Count Percent
loadit>    Histogram-KG:...........................        78   2    0.21%
loadit>    Histogram-KG:                                   81   4    0.64%
loadit>    Histogram-KG:                                   83   1    0.75%
loadit>    Histogram-KG:                                   85   6    1.39%
loadit>    Histogram-KG:...........................        86   1    1.49%
loadit>    Histogram-KG:                                   87   4    1.92%
loadit>    Histogram-KG:                                   88   3    2.24%
loadit>    Histogram-KG:                                   89  14    3.73%
loadit>    Histogram-KG:...........................        90  10    4.80%
loadit>    Histogram-KG:                                   91  16    6.50%
loadit>    Histogram-KG:                                   92   9    7.46%
loadit>    Histogram-KG:                                   93  17    9.28%
loadit>    Histogram-KG:...........................        94  19   11.30%
loadit>    Histogram-KG:                                   95  15   12.90%
loadit>    Histogram-KG:                                   96  32   16.31%
loadit>    Histogram-KG:                                   97  31   19.62%
loadit>    Histogram-KG:...........................        98  29   22.71%
loadit>    Histogram-KG:                                   99  40   26.97%
loadit>    Histogram-KG:                                  100  45   31.77%
loadit>    Histogram-KG:                                  101  25   34.43%
loadit>    Histogram-KG:...........................       102  31   37.74%
loadit>    Histogram-KG:                                  103  28   40.72%
loadit>    Histogram-KG:                                  104  34   44.35%
loadit>    Histogram-KG:                                  105  46   49.25%
loadit>    Histogram-KG:...........................       106  30   52.45%
loadit>    Histogram-KG:                                  107  34   56.08%
loadit>    Histogram-KG:                                  108  40   60.34%
loadit>    Histogram-KG:                                  109  21   62.58%
loadit>    Histogram-KG:...........................       110  34   66.20%
loadit>    Histogram-KG:                                  111  37   70.15%
loadit>    Histogram-KG:                                  112  48   75.27%
loadit>    Histogram-KG:                                  113  27   78.14%
loadit>    Histogram-KG:...........................       114  28   81.13%
loadit>    Histogram-KG:                                  115  21   83.37%
loadit>    Histogram-KG:                                  116  18   85.29%
loadit>    Histogram-KG:                                  117   6   85.93%
loadit>    Histogram-KG:...........................       118  17   87.74%
loadit>    Histogram-KG:                                  119  16   89.45%
loadit>    Histogram-KG:                                  120  23   91.90%
loadit>    Histogram-KG:                                  121  25   94.56%
loadit>    Histogram-KG:...........................       122   7   95.31%
loadit>    Histogram-KG:                                  123  10   96.38%
loadit>    Histogram-KG:                                  124  15   97.97%
loadit>    Histogram-KG:                                  125   3   98.29%
loadit>    Histogram-KG:...........................       126   3   98.61%
loadit>    Histogram-KG:                                  128   3   98.93%
loadit>    Histogram-KG:                                  129   3   99.25%
loadit>    Histogram-KG:                                  132   2   99.47%
loadit>    Histogram-KG:...........................       134   2   99.68%
loadit>    Histogram-KG:                                  144   2   99.89%
loadit>    Histogram-KG:                                  145   1  100.00%

Field	Description
KeyLen	This is the length of the Identity Data after compression
Count	This is the number of records with that length
Percent	This is the cumulative percentage of the number of records having lengths less than or equal to the current KeyLen

Segment Lengths

The histogram can be converted into more useful data by running

%SSABIN%\histkg ReportFile

where

ReportFile

is the name of the log file containing the Table Loader output. It will produce a report similar to the one below:


Block Size      1792
Block Overhead     0
Index Name    idx_addr_fullkeydata
KeyData          225
CompLen          200

      key len    count     %   bytes   comp-1    comp-2   segs
           78       2   0.21     156      430       440      2
           81       4   0.64     324      860       880      4
           83       1   0.75      83      215       220      1
           85       6   1.39     510     1290      1320      6
           86       1   1.49      86      215       220      1
           87       4   1.92     348      860       880      4
           88       3   2.24     264      645       660      3
           89      14   3.73    1246     3010      3080     14
           90      10   4.8      900     2150      2200     10
           91      16   6.5     1456     3440      3520     16
           92       9  7.46      828     1935      1980      9
           93      17  9.28     1581     3655      3740     17
           94      19  11.3     1786     4085      4180     19
           95      15  12.9     1425     3225      3300     15
           96      32  16.31    3072     6880      7040     32
           97      31  19.62    3007     6665      6820     31
           98      29  22.71    2842     6235      6380     29
           99      40  26.97    3960     8600      8800     40
          100      45  31.77    4500     9675      9900     45
          101      25  34.43    2525     5375      5500     25
          102      31  37.74    3162     6665      6820     31
          103      28  40.72    2884     6020      6160     28
          104      34  44.35    3536     7310      7480     34
          105      46  49.25    4830     9890     10120     46
          106      30  52.45    3180     6450      6600     30
          107      34  56.08    3638     7310      7480     34
          108      40  60.34    4320     8600      8800     40
          109      21  62.58    2289     4515      4620     21
          110      34  66.2     3740     7310      7480     34
          111      37  70.15    4107     7955      8140     37
          112      48  75.27    5376    10320     10560     48
          113      27  78.14    3051     5805      5940     27
          114      28  81.13    3192     6020      6160     28
          115      21  83.37    2415     4515      4620     21
          116      18  85.29    2088     3870      3960     18
          117       6  85.93     702     1290      1320      6
          118      17  87.74    2006     3655      3740     17
          119      16  89.45    1904     3440      3520     16
          120      23  91.9     2760     4945      5060     23
          121      25  94.56    3025     5375      5500     25
          122       7  95.31     854     1505      1540      7
          123      10  96.38    1230     2150      2200     10
          124      15  97.97    1860     3225      3300     15
          125       3  98.29     375      645       660      3
          126       3  98.61     378      645       660      3
          128       3  98.93     384      645       660      3
          129       3  99.25     387      645       660      3
          132       2  99.47     264      430       440      2
          134       2  99.68     268      430       440      2
          144       2  99.89     288      430       440      2
          145       1 100        145      215       220      1
    Total 938                  99537   201670    206360    938

Keydata Offset    11
Key Overhead       4
Block Size      1792
Block Overhead     0

    compLen      bytes    segs    segs/key   DB-bytes   DB-blocks
        1      2090277   99537     106.116    2488425        1389
        2      1099868   49994      53.299    1299844         726
        3       770201   33487      35.700     904149         505
        4       605232   25218      26.885     706104         395
        5       506825   20273      21.613     587917         329
        6       441220   16970      18.092     509100         285
        7       394659   14617      15.583     453127         253
        8       360136   12862      13.712     411584         230
        9       332717   11473      12.231     378609         212
       10       311280   10376      11.062     352784         197
       11       293942    9482      10.109     331870         186
       12       279552    8736       9.313     314496         176
       13       267465    8105       8.641     299885         168
       14       256088    7532       8.030     286216         160
       15       247660    7076       7.544     275964         154
       16       239544    6654       7.094     266160         149
       17       233137    6301       6.717     258341         145
       18       226822    5969       6.364     250698         140
       19       221871    5689       6.065     244627         137
       20       216280    5407       5.764     237908         133
       21       212052    5172       5.514     232740         130
       22       209622    4991       5.321     229586         129
       23       205497    4779       5.095     224613         126
       24       203016    4614       4.919     221472         124
       25       198360    4408       4.699     215992         121
       26       196834    4279       4.562     213950         120
       27       193687    4121       4.393     210171         118
       28       191040    3980       4.243     206960         116
       29       189728    3872       4.128     205216         115
       30       189150    3783       4.033     204282         114
       31       187884    3684       3.928     202620         114
       32       187668    3609       3.848     202104         113
       33       185712    3504       3.736     199728         112
       34       183654    3401       3.626     197258         111
       35       181115    3293       3.511     194287         109
       36       178472    3187       3.398     191220         107
       37       176358    3094       3.299     188734         106
       38       173478    2991       3.189     185442         104
       39       173696    2944       3.139     185472         104
       40       173280    2888       3.079     184832         104
       41       173362    2842       3.030     184730         104
       42       174840    2820       3.006     186120         104
       43       176841    2807       2.993     188069         105
       44       179072    2798       2.983     190264         107
       45       180180    2772       2.955     191268         107
       46       181302    2747       2.929     192290         108
       47       181637    2711       2.890     192481         108
       48       181016    2662       2.838     191664         107
       49       179469    2601       2.773     189873         106
       50       176120    2516       2.682     186184         104
       51       174660    2460       2.623     184500         103
       52       172656    2398       2.557     182248         102
       53       169506    2322       2.475     178794         100
       54       166352    2248       2.397     175344          98
       55       164475    2193       2.338     173247          97
       56       160208    2108       2.247     168640          95
       57       158081    2053       2.189     166293          93
       58       157092    2014       2.147     165148          93
       59       157289    1991       2.123     165253          93
       60       156160    1952       2.081     163968          92
       61       155520    1920       2.047     163200          92
       62       155390    1895       2.020     162970          91
       63       156787    1889       2.014     164343          92
       64       158424    1886       2.011     165968          93
       65       160055    1883       2.007     167587          94
       66       161766    1881       2.005     169290          95
       67       163473    1879       2.003     170989          96
       68       165352    1879       2.003     172868          97
       69       167231    1879       2.003     174747          98
       70       169110    1879       2.003     176626          99
       71       170989    1879       2.003     178505         100
       72       172684    1877       2.001     180192         101
       73       174468    1876       2.000     181972         102
       74       176344    1876       2.000     183848         103
       75       178220    1876       2.000     185724         104
       76       180096    1876       2.000     187600         105
       77       181972    1876       2.000     189476         106
       78       183652    1874       1.998     191148         107
       79       185526    1874       1.998     193022         108
       80       187400    1874       1.998     194896         109
       81       188870    1870       1.994     196350         110
       82       190740    1870       1.994     198220         111
       83       192507    1869       1.993     199983         112
       84       194376    1869       1.993     201852         113
       85       195615    1863       1.986     203067         114
       86       197372    1862       1.985     204820         115
       87       198806    1858       1.981     206238         116
       88       200340    1855       1.978     207760         116
       89       200669    1841       1.963     208033         117
       90       201410    1831       1.952     208734         117
       91       201465    1815       1.935     208725         117
       92       202272    1806       1.925     209496         117
       93       202157    1789       1.907     209313         117
       94       201780    1770       1.887     208860         117
       95       201825    1755       1.871     208845         117
       96       199868    1723       1.837     206760         116
       97       197964    1692       1.804     204732         115
       98       196234    1663       1.773     202886         114
       99       193137    1623       1.730     199629         112
      100       189360    1578       1.682     195672         110
      101       187913    1553       1.656     194125         109
      102       185684    1522       1.623     191772         108
      103       183762    1494       1.593     189738         106
      104       181040    1460       1.557     186880         105
      105       176750    1414       1.507     182406         102
      106       174384    1384       1.475     179920         101
      107       171450    1350       1.439     176850          99
      108       167680    1310       1.397     172920          97
      109       166281    1289       1.374     171437          96
      110       163150    1255       1.338     168170          94
      111       159558    1218       1.299     164430          92
      112       154440    1170       1.247     159120          89
      113       152019    1143       1.219     156591          88
      114       149410    1115       1.189     153870          86
      115       147690    1094       1.166     152066          85
      116       146336    1076       1.147     150640          85
      117       146590    1070       1.141     150870          85
      118       145314    1053       1.123     149526          84
      119       144143    1037       1.106     148291          83
      120       141960    1014       1.081     146016          82
      121       139449     989       1.054     143405          81
      122       139444     982       1.047     143372          81
      123       138996     972       1.036     142884          80
      124       137808     957       1.020     141636          80
      125       138330     954       1.017     142146          80
      126       138846     951       1.014     142650          80
      127       139797     951       1.014     143601          81
      128       140304     948       1.011     144096          81
      129       140805     945       1.007     144585          81
      130       141750     945       1.007     145530          82
      131       142695     945       1.007     146475          82
      132       143336     943       1.005     147108          83
      133       144279     943       1.005     148051          83
      134       144914     941       1.003     148678          83
      135       145855     941       1.003     149619          84
      136       146796     941       1.003     150560          85
      137       147737     941       1.003     151501          85
      138       148678     941       1.003     152442          86
      139       149619     941       1.003     153383          86
      140       150560     941       1.003     154324          87
      141       151501     941       1.003     155265          87
      142       152442     941       1.003     156206          88
      143       153383     941       1.003     157147          88
      144       153996     939       1.001     157752          89
      145       154770     938       1.000     158522          89

The first section of the report summarizes the histogram.

Field	Description
Block Size	Database Block Size. Information only.
Block Overhead	Database overhead when storing records within a block including control structures and padding. Information only.
Index Name	The name of the IDX
KeyData	The sum of the length of the IDT columns (the Identity Data, uncompressed)
CompLen	Compress-Key-Data(n) value
KeyLen	The length of the Identity Data after compression
Count	The number of records with this KeyLen
Bytes	KeyLen * Count
Comp-1	Total bytes to store Count records with KeyLen using Method 1 (1 segment only)
Comp-2	Total bytes to store Count records with KeyLen using Method 0 (multiple segments)
Segs	The number of segments required to store Count records of KeyLen

The second part of the report gives space estimates for various values of

Compress-Key-Data

Field	Description
KeyDataOffset	Length of the Fuzzy Key including any partition
KeyOverhead	The overhead associated with storing the segment in the database
Block Size	Database Block Size. Information only.
Block Overhead	Database overhead when storing records within a block including control structures and padding. Information only.
compLen	n from Compress-Key-Data(n)
Bytes	The number of bytes required to store segments of this size
Segs	The number of segments used
Segs/Key	The average number of segments per IDX record.
DB-Bytes	The number of bytes for segment of this size (scaled up by KeyOverhead )
DB-Blocks	The number of blocks for segments of this size (based on the Blocksize and BlockOverhead )