 
| Subject: | |
| From: | |
| Reply To: | |
| Date: | Wed, 28 Aug 1996 03:53:19 UT |
| Content-Type: | text/plain |
| Parts/Attachments: |
|
|
I have several questions pertaining to large Image databases. I am currently
working at a client whose database is growing and they are concerned about
capacity and performance problems they are suffering. Currently, due to a
consultant's recommendation, they are a path to split out their database into
two identical databases each approx. having half the records. Then, they will
be using Netbase to shadow both databases into one large database for
reporting purposes. I strongly recommended against doing this since I feel
that image can easily handle the number of records they are working with.
Their database is currently approx. 150 datasets with the largest being 18.5
million records. Most datasets are in the 100k-1m entry range. The physical
size of the database has grown to over 60 million sectors. Below if a
Suprtool FO SETS command showing the database:
Database: HEALTH.DATA.AIH TPI: Omnidex 3.02.08
Set Item Entry Load Entry
Sets: Num Type Count Capacity Count Factor Length B/F
ADDR-WHO-A 1 A 1 643523 454699 71 % 9 51
ADDRTYPE-WHO-A 2 A 1 675739 477899 71 % 11 46
AFFILIATION-A 3 A 1 504983 361357 72 % 8 53
AGECATDEF-A 4 A 1 10007 14 0 % 6 60
AGEKEY-A 5 A 1 10007 13 0 % 1 84
ALT-KEY-A 6 A 1 61001 42913 70 % 6 60
AUTH-DETAIL-A 7 A 1 846353 593900 70 % 8 15
AUTH-TEMPLATE-M 8 M 5 10009 8 0 % 45 18
AUTHORIZATION-A 9 A 1 424001 301726 71 % 6 17
BENEFIT-M 10 M 32 12253 2321 19 % 101 9
BENEFIT-PKG-M 11 M 5 10151 251 2 % 44 18
BOARDCERT-A 12 A 1 10007 12 0 % 1 84
BP-BENEFIT-A 13 A 1 44867 31804 71 % 5 63
BP-EXPLODE-A 14 A 1 987433 694017 70 % 5 63
CALL-PAIR-A 15 A 1 10009 11 0 % 18 35
CALL-WHO-A 16 A 1 10009 12 0 % 9 39
CAP-EXPLODE-A 17 A 1 3623507 293283 8 % 32 23
CAP-RATE-NAME-A 18 A 1 109741 9175 8 % 4 30
CAPFACTOR-DEF-A 19 A 1 100003 0 0 % 5 46
CAPFACTOR-ID-A 20 A 1 100003 0 0 % 2 78
CC-EXCLUDE-M 21 M 1 10007 0 0 % 5 101
CLAIM-A 22 A 1 3611323 2532856 70 % 6 21
CLXREF-A 23 A 1 202777 142850 70 % 6 60
CODE-A 24 A 1 17231 7314 42 % 5 63
CODE-M 25 M 5 17231 7321 42 % 46 17
COMMAND-M 26 M 6 10007 11 0 % 83 11
COMP-KEY-A 27 A 1 91753 64573 70 % 11 46
COMP-KEY-CTR-A 28 A 1 10007 1 0 % 16 37
COMPONENT-M 29 M 5 10711 857 8 % 45 18
CONTRACT-A 30 A 1 351179 250752 71 % 5 19
CONTROL-A 31 A 1 45007 32098 71 % 6 60
CORPORATION-M 32 M 29 10007 6 0 % 118 8
COUNTER-A 33 A 1 496871 354558 71 % 21 26
COUNTER-M 34 M 10 10211 299 3 % 54 17
DIAG-A 35 A 1 25693 15683 61 % 5 63
DIAG-M 36 M 5 25577 15576 61 % 46 16
DISCHGPLAN-A 37 A 1 10007 2 0 % 6 29
DIVISION-A 38 A 1 15971 5977 37 % 5 46
DOCUMENT-A 39 A 1 15013 1 0 % 6 44
DRG-DIAG-A 40 A 1 10007 0 0 % 3 72
DRG-M 41 M 13 10663 663 6 % 61 14
DRG-PROC-A 42 A 1 10007 0 0 % 3 72
DRUG-M 43 M 5 18013 8270 46 % 49 17
DRUG-TABLE-A 44 A 1 18013 8270 46 % 8 53
EPISODE-A 45 A 1 321889 221951 69 % 11 15
ERRORS-M 46 M 9 10949 1150 11 % 47 15
FEE-SCHEDULE-A 47 A 1 253343 178525 70 % 17 30
FLAG-KEY-A 48 A 1 10037 25 0 % 4 67
FUNCTIONS-M 49 M 14 1305097 915594 70 % 76 12
FUND-M 50 M 2 10007 38 0 % 17 25
GROUP-M 51 M 36 12097 2207 18 % 153 6
GROUP-PRACTICE-M 52 M 11 18041 8363 46 % 37 24
IRS-A 53 A 1 76123 54242 71 % 5 63
KEYWORDS-M 54 M 6 42821 30224 71 % 23 25
LOS-GROUP-M 55 M 6 10007 0 0 % 46 20
LOS-TABLE-M 56 M 14 10007 0 0 % 32 27
MDC-M 57 M 2 10037 26 0 % 40 20
MEDICAID-A 58 A 1 12527 2673 21 % 6 60
MEDICARE-A 59 A 1 13001 3211 25 % 6 60
MEMAUTH-A 60 A 1 113173 80734 71 % 6 14
MEMBER-A 61 A 1 607249 426387 70 % 6 14
NUMBER-GEN-M 62 M 6 12197 2441 20 % 55 17
OFFICE-M 63 M 8 67103 47882 71 % 24 29
OTHCOVDEF-A 64 A 1 10061 47 0 % 22 30
PAY-CLASS-M 65 M 2 11471 1577 14 % 40 20
PAYABLE-A 66 A 1 596537 424043 71 % 6 60
PAYEEWHO-A 67 A 1 34147 24401 71 % 7 56
PERIOD-KEY-A 68 A 1 10037 12 0 % 5 63
PERIODTAB-M 69 M 3 10007 1 0 % 4 67
POOL-M 70 M 2 10007 14 0 % 40 20
POOLCLASS-A 71 A 1 10007 21 0 % 2 78
PREFIX-A 72 A 1 10133 124 1 % 3 72
PREM-RATE-A 73 A 1 22787 12835 56 % 30 19
PROC-A 74 A 1 29917 19906 67 % 5 63
PROC-M 75 M 5 28211 18466 65 % 46 16
PROVIDER-A 76 A 1 115057 81044 70 % 6 24
REMARK-CAT-A 77 A 1 1344151 942607 70 % 13 42
REMARK-SEQ-A 78 A 1 1462651 1025051 70 % 15 39
REMARK-WHO-A 79 A 1 1330633 933129 70 % 12 44
RIDER-M 80 M 8 10243 408 4 % 51 16
ROLLUP-A 81 A 1 14713 4857 33 % 5 63
SERVICE-A 82 A 1 6148531 4314254 70 % 8 18
SES-NAME-A 83 A 1 11383 1436 13 % 4 67
SES-OP-A 84 A 1 11383 1436 13 % 2 78
SPEC-CAP-M 85 M 5 12413 2389 19 % 40 22
SSN-A 86 A 1 238897 167603 70 % 5 46
SUPER-TABLE-A 87 A 1 11213 1431 13 % 16 37
TABLE-A 88 A 1 10037 37 0 % 2 27
WHO-A 89 A 1 181361 127350 70 % 8 53
ZIP-M 90 M 9 61813 43295 70 % 30 29
ADDRESS 91 D 23 614040 491863 80 % 146 6
ADMISSION 92 D 104 34500 23783 69 % 291 3
ADMISSION-AUDIT 93 D 91 25245 15261 60 % 264 3
ADMISSION-REVIEW 94 D 15 10000 0 0 % 43 16
AFFILIATION 95 D 50 514283 416905 81 % 121 7
AGECATEGORY 96 D 8 10080 195 2 % 20 35
AGEKEY-SPAN 97 D 8 10010 14 0 % 19 35
ALT-PROVIDER 98 D 39 851532 682232 80 % 81 12
AUDIT 99 D 10 10020 0 0 % 32 30
AUTH-COUNTER 100 D 9 473044 316875 67 % 38 22
AUTH-DET-AUDIT 101 D 65 49604 39685 80 % 188 4
AUTH-DETAIL 102 D 72 705408 570114 81 % 204 4
AUTH-DRUG 103 D 36 10003 0 0 % 115 7
AUTH-FLAGS 104 D 38 10410 428 4 % 94 10
AUTH-TEMPLATE 105 D 19 10017 8 0 % 37 21
AUTHORIZ-AUDIT 106 D 40 64155 52055 81 % 126 7
AUTHORIZATION 107 D 44 369705 301726 82 % 133 7
BENEFICIARY 108 D 15 10665 657 6 % 56 15
BP-EXPLODE 109 D 7 22562010 18300391 81 % 25 30
BR-LINK 110 D 9 14434 4662 32 % 57 14
CALL 111 D 9 10020 11 0 % 52 15
CAP-EXPLODE 112 D 6 3623508 298685 8 % 46 14
CAP-RATE 113 D 10 600220 57575 10 % 35 20
CAP-RATE-DEF 114 D 35 122240 11291 9 % 72 10
CAP-RATE-RETRO 115 D 13 100012 0 0 % 42 22
CAPFACTOR 116 D 5 100030 0 0 % 15 35
CAPFACTOR-HDR 117 D 12 100000 0 0 % 42 20
CB-LINK 118 D 7 14800 4990 34 % 21 40
CERTIFICATION 119 D 10 10020 163 2 % 25 30
CLAIM 120 D 41 3184064 2532821 80 % 111 8
CLXREF 121 D 2 181500 145300 80 % 12 25
COBRA 122 D 35 10000 12 0 % 106 8
CODE-DETAIL 123 D 10 17059 7314 43 % 128 7
COMP-KEYWORDS 124 D 8 105762 85090 80 % 246 3
CONCURRENT-REV 125 D 42 37784 27391 72 % 108 8
CONTRACT 126 D 26 308528 250748 81 % 112 8
CONTRACT-SPAN 127 D 34 838964 673239 80 % 69 14
COUNTER 128 D 10 2635335 2081702 79 % 55 15
CTR-MESSAGE 129 D 8 10024 0 0 % 23 28
DENTAL-AUTH 130 D 5 10016 2 0 % 57 16
DENTAL-HISTORY 131 D 7 95774 78195 82 % 64 14
DIAG-DETAIL 132 D 18 25683 15683 61 % 36 21
DIAG-EXT 133 D 3 10013 0 0 % 41 17
DIVISION 134 D 43 15705 5977 38 % 164 5
DIVISION-SPAN 135 D 18 26598 16680 63 % 36 22
DOCUMENT 136 D 5 10005 0 0 % 61 15
DRG 137 D 11 10494 493 5 % 156 6
DRG-DIAG 138 D 7 10016 0 0 % 26 32
DRG-PROC 139 D 4 10008 0 0 % 92 9
DRUG 140 D 32 18018 8270 46 % 95 9
EPISODE-DETAIL 141 D 24 10020 44 0 % 68 10
ERRORS 142 D 6 10352 342 3 % 51 16
ERRORS-EXT 143 D 5 10668 660 6 % 50 14
EXT-REMARK 144 D 3 3191903 2558218 80 % 53 11
FEE-SCHEDULE 145 D 15 315000 253875 81 % 50 18
FUND 146 D 5 10028 38 0 % 18 46
GLOBAL 147 D 8 10001 1 0 % 13 1
GROUP-SPAN 148 D 11 14088 4279 30 % 26 24
HIAA-FEE 149 D 17 10008 0 0 % 54 12
IN-PROCESS 150 D 13 717744 579092 81 % 34 24
KEYWORDS 151 D 8 226350 183327 81 % 61 15
MEM-CONDITION 152 D 18 10794 970 9 % 154 6
MEMBER 153 D 24 532252 426384 80 % 93 7
MEMBER-INFO 154 D 11 12870 2873 22 % 35 26
MEMBER-SPAN 155 D 38 1854552 1497716 81 % 80 12
OPINION 156 D 14 10010 5 0 % 49 13
OTHCOV-FORMULA 157 D 22 10050 48 0 % 64 15
OTHCOV-SPAN 158 D 48 13620 3625 27 % 92 10
PAY-QUALIFIER 159 D 72 15125 5151 34 % 168 5
PERIOD-DETAIL 160 D 5 10023 12 0 % 18 39
POOLCLASS-DETAIL 161 D 6 10016 195 2 % 20 32
PREM-RATE 162 D 10 28413 18524 65 % 86 11
PREM-RATE-RETRO 163 D 16 12885 1496 12 % 61 15
PREM-RATE-RIDER 164 D 23 92547 74064 80 % 74 13
PROC-DETAIL 165 D 22 29379 19906 68 % 36 21
PROC-EXT 166 D 3 27642 17923 65 % 41 17
PROV-CONDITION 167 D 16 10008 8 0 % 118 8
PROV-OFFICE 168 D 5 17556 7553 43 % 15 44
PROVIDER 169 D 47 100768 81043 80 % 110 8
RC-LINK 170 D 7 12330 2348 19 % 21 30
REDCOV-SPAN 171 D 14 10032 0 0 % 38 24
REMARK-SUMMARY 172 D 14 1631439 1307734 80 % 91 9
SERVICE 173 D 88 4191284 3332985 80 % 232 4
SERVICE-ACCTNG 174 D 14 4426510 3574776 81 % 77 10
SERVICE-AUDIT 175 D 92 184584 147665 80 % 235 4
SERVICE-DRUG 176 D 80 1226780 981423 80 % 219 4
SES-DATA 177 D 34 11376 1436 13 % 150 6
SUPER-TABLE 178 D 8 11417 1452 13 % 133 7
Another thing to consider is this database is from a canned package and the
structure of the database cannot be changed dramatically. The company who put
this database together used alot of Automatic Masters and many image sort
paths. Since then they have begun using OMNIDEX but still rely on many of the
image paths and sort items. I just wanted to know if anyone else has had
experience with large image databases. I would like to get some of the
following questions answered if possible.
1. Does this database seem unusually large ?
2. Does anyone see problems with the current capacities/blocking factors?
3. The DBA tends to use the old 80% fullness rule for masters. Is this still
true on datasets where there are 4 million entries and the 80% rule says to
set the capacity to 5 million?
4. What can be done to improve database performance ?
5. What database spreading techniques do you recommend on a database of this
size?
6. Does any one use DDE to expand the capacity of their databases and what
are pros/cons?
7. Does anyone else other than me think that image can handle a database of
this size?
Any information on this subject would be greatly appreciated. Thanks in
advance for your help.
Steven N. Lastic
SW Consulting, Inc.
Email: [log in to unmask]
1-800-494-4977 or 941-656-1011
|
|
|
