Au Contraire Using an Index with a limited number of differing values will reduce the SELECT time when Selecting a specific value Say for example in the extreme case of ( only ) four different 'CURRENCIES' within the items in your file, eg :
1 million items with a 'CURRENCY' of 'GBP' 1 million items with a 'CURRENCY' of 'USD' 1 million items with a 'CURRENCY' of 'YEN' and 1 thousand items with a 'CURRENCY' of 'FF' SELECTing the million item ids for items with a 'CURRENCY' of 'USD' will be instantaneous via the index, compared with processing the '3,001,000' items looking for a 'CURRENCY' of 'USD' The downside is Creating / Updating an Index where the majority of values for the indexed fielsd are identical However in jBASE 4.1 and above, the Creation / Update of an index on such non unique values is nowhere near as painful / time consuming as previously in jBASE 4.0 and prior releases And is also improved by creating the index on such fields using the '- w' option Pat. On 4 Jan, 10:57, Mike Preece <[email protected]> wrote: > Taking a more simplistic view... it is often counter-productive to > create and use indexes. This is true in cases where a very high > proportion of the data on which the index is based is similar. For > example, suppose the CURRENCY is almost always the same. The index for > CURRENCY will be enormous with consequential performance degradation. > In this case it would be better to do a straight SELECT without > selection criteria and have a simple piece of code after the READNEXT > to ignore items with a CURRENCY other than that in which you're > interested. > > Can you tell us the total number of records in the file and the number > of variations for each of the indexed attributes? > > On Dec 29 2009, 3:14 pm, "[Xze]" <[email protected]> wrote: > > > Dear all, > > > We have noticed a huge performace impact on the system when we are running > > SELECT on INDEX fields for FBNK.ACCOUNT file. > > > After every SELECT the kernel was allocating additional space in RAM. > > When both RAM and paging space were 99% used, the kernel killed almost all > > jbase sessions. > > > LIST-INDEX FBNK.ACCOUNT > > > INDEX definitions for file FBNK.ACCOUNT at 16:54:38 29 DEC > > 2009 PAGE 1 > > INDEX NAME LOCALE NAME SORT KEYS. LOOKUP.... INDEX > > DEFINITION................... > > ACCOUNT.OF en_US AR BY-AR 11 > > FICER > > CATEGORY en_US AR BY-AR 2 > > CURRENCY en_US AL BY-AL 8 > > CUSTOMER en_US AR BY-AR 1 > > SYN.CODE en_US AR BY-AR > > ITYPE(\LOCAL.REF<1,7>\) > > 5 Records Listed > > > VERIFY-DISTRIB FBNK.ACCOUNT > > Partitioning Algorithm is USER Subroutine 'AC.11' > > User subroutine OK. > > Part file 'FBNK.ACCOUNT.01', part number 1 - OK > > Part file 'FBNK.ACCOUNT.02', part number 2 - OK > > Part file 'FBNK.ACCOUNT.03', part number 3 - OK > > Part file 'FBNK.ACCOUNT.04', part number 4 - OK > > Part file 'FBNK.ACCOUNT.05', part number 5 - OK > > Part file 'FBNK.ACCOUNT.06', part number 6 - OK > > Part file 'FBNK.ACCOUNT.07', part number 7 - OK > > Part file 'FBNK.ACCOUNT.08', part number 8 - OK > > Part file 'FBNK.ACCOUNT.09', part number 9 - OK > > Part file 'FBNK.ACCOUNT.10', part number 10 - OK > > > SUBROUTINE AC.11(DUMMY,INID,RESULT) > > DEFC JLibBCONV_IB(VAR) > > IF NUM(INID[1]) THEN > > PARTNO = INID[1] > > PARTNO++ > > END ELSE > > PARTNO = 11 > > END > > RESULT = JLibBCONV_IB(PARTNO) > > RETURN > > END > > > In order to to replicate the issue we have opened 10 jbase sessions and each > > performed 50 SELECTs on FBNK.ACCOUNT INDEX fileds. > > > At the end, the mw42 -m output is as follows: > > > Port User Pid Files Perf Del Read Write Open MemF MemU > > Cpu Prog > > 1 uatusr 279002 6 (5) 1 1 11 2 11 0 9.39M > > 0.00 1 E /opt/jbase5/bin/jsh -s jsh - (jsh. > > 2 uatusr 389366 7 (6) 1 1 2567 1 10 0 2.06M > > 0.93 2 I mw42 -m (mw42.b,232) > > 4 uatusr 225790 7 (6) 197 1 3300 99 1433 0 > > 433M18.83 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 5 uatusr 229884 7 (6) 197 1 3291 99 1433 0 > > 466M 19.98 > > 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 12 uatusr 340238 7 (6) 193 1 3214 97 1404 0 > > 519M22.05 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 14 uatusr 274452 7 (6) 201 1 3391 101 1462 0 > > 315M13.91 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 19 uatusr 413718 7 (6) 197 1 3321 99 1433 0 > > 422M 16.51 > > 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 20 uatusr 110934 7 (6) 193 1 3218 97 1404 0 > > 506M22.17 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 21 uatusr 352482 7 (6) 197 1 3302 99 1433 0 > > 365M 15.73 > > 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 22 uatusr 364998 33 (19) 192 1 3232 97 1402 0 411M > > 0.00 3 SELECT FBNK.ACCOUNT WITH CURRENCY EQ > > 27 uatusr 938076 7 (6) 197 1 3279 99 1433 0 > > 553M24.51 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 30 uatusr 139574 6 (5) 3 1 886 7 22 0 2.06M > > 0.32 1 E /opt/jbase5/bin/jsh -s jsh - (jsh. > > 35 uatusr 635036 7 (6) 193 1 3252 97 1404 0 > > 314M14.75 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > * 36 uatusr 287038 6 (5) 0 1 902 0 6 0 9.19M > > 0.29 1 mw42 -m (mw42.b,764) > > 38 uatusr 373142 58 (50) 22 4 626 16 154 0 12.7M > > 0.18 1 I EX (S.COMMUNICATION,254) > > > The same test on identical area, but WITHOUT INDEX: > > > 14 uatpf 139592 6 (5) 1 1 14 3 13 0 9.39M > > 0.00 1 E /opt/jbase5/bin/jsh -s jsh - (jsh. > > 19 uatpf 315638 7 (6) 181 1 76.4M 91 1317 0 37.8M > > 14m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 20 uatpf 209012 7 (6) 189 1 79.8M 95 1375 0 29.7M > > 14m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 21 uatpf 176290 7 (6) 181 1 76.4M 91 1317 0 33.7M > > 14m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 22 uatpf 262642 7 (6) 189 1 79.8M 95 1375 0 36.2M > > 14m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 27 uatpf 405766 7 (6) 181 1 76.4M 91 1317 0 31.7M > > 14m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 30 uatpf 139372 7 (6) 177 1 74.7M 89 1288 0 31.7M > > 13m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 35 uatpf 225538 7 (6) 185 1 78.1M 93 1346 0 38.2M > > 14m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 36 uatpf 188786 7 (6) 181 1 76.4M 91 1317 0 37.6M > > 14m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 39 uatpf 450742 7 (6) 181 1 76.4M 91 1317 0 37.6M > > 13m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > 40 uatpf 94562 33 (19) 180 1 75.3M 91 1315 0 37.6M > > 13m 3 SELECT FBNK.ACCOUNT WITH SYN.CODE EQ > > * 41 uatpf 151944 6 (5) 0 1 51209 0 6 0 9.39M > > 13.43 1 mw42 -m (mw42.b,764) > > > ( The full mw42 -m output is attached ) > > > In ther first case every next SELECT was taking additional memory and was > > not releasing it untill the session is closed. > > > We are using: > > OS - AIX 5.3.9.0 > > jB - Major 5.0 , Minor 20 , Patch 0364 (Change 85159) > > > jdiag - jBASE diagnostic '$Revision: 1.15 $' > > System Information > > ================== > > System : AIX jbsec 3.5 00CED1BC4C00 > > OS Release : 5.3.9.0 > > UNIX User : uatusr (uid 213, euid 213) > > Tty name : /dev/pts/1 > > Time : Tue Dec 29 16:38:20 2009 > > Environment > > =========== > > JBCPORTNO : Not Set > > JBCRELEASEDIR : '/opt/jbase5' > > JBCGLOBALDIR : '/opt/jbase5' > > WARNING: JBCDATADIR is not set, Default '/opt/jbase5/jbase_data' > > WARNING: JBCDATADIR is subdirectory of JBCGLOBALDIR > > HOME : '/eoy/eoy/bnk.run' > > JEDIFILEPATH : '/eoy/eoy/bnk.run' > > JEDIFILENAME_MD : 'VOC' > > JEDIFILENAME_SYSTEM : '/opt/jbase5/src/SYSTEM' > > SYSTEM File is (DICT) : '/opt/jbase5/src/SYSTEM]D' > > RELEASE Information : Major 5.0 , Minor 20 , Patch 0364 (Change > > 85159) > > Spooler dir (JBCSPOOLERDIR) : '/var/spool/jbase' > > JBCEMULATE : 'prime' > > WARNING: Cannot access Executable path '/eoy/eoy/bnk.run/globuspatchbin', > > error 2 > > Object path (JBCOBJECTLIST) : > > '/eoy/eoy/bnk.run/globuspatchlib:/eoy/eoy/bnk.run/lib:/eoy/eoy/bnk.run/globuslib:/eoy/eoy/bnk.run/fixlib' > > WARNING: Cannot access Object path '/eoy/eoy/bnk.run/globuspatchlib', error > > 2 > > WARNING: Cannot access Object path '/eoy/eoy/bnk.run/fixlib', error 2 > > jBASE Compiler Run-time : '/opt/jbase5/config/system.properties' > > Program dir (JBCDEV_BIN) : '/eoy/eoy/bnk.run/bin' > > Subroutine dir (JBCDEV_LIB) : '/eoy/eoy/bnk.run/lib' > > Max open files : 65534 > > > Can anybody please explain what is triggering this and how to fix the issue? > > > mw42.zip > > 100KViewDownload
-- Please read the posting guidelines at: http://groups.google.com/group/jBASE/web/Posting%20Guidelines IMPORTANT: Type T24: at the start of the subject line for questions specific to Globus/T24 To post, send email to [email protected] To unsubscribe, send email to [email protected] For more options, visit this group at http://groups.google.com/group/jBASE?hl=en
