On Jan 5, 4:29 pm, pat <[email protected]> wrote: > 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' >
I await with interest the actual figures from the OP. I wonder whether your suggested figures for an "extreme case" reflects real life. Perhaps the figures for one currency will almost always represent way more than 30% of data. > 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 > > ... > > read more »
-- 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
