CREATE-INDEX issue

[foxy]

Hi,

I have noticed some strange behavior of CREATE-INDEX command.
My database has around 500,000 records.

case 1 :

If a create an index on a field, that has one or two distinct values
the indexing process takes forever (i canceled it after 12 hours of
indexing).

case 2 :

if I create index on a field, that has several thousands of distinct
values - it takes 1.5 - 2 hours.

case 3 :

if I create index on a field that is unique (I mean all records has
different values in this field) - the whole process takes several
minutes.

So, here is obvious an algorithm problem.
I think that this building routine of jBase uses dynamic arrays and
once they grow up in size - it's more hard to work with them.

In case 1 the first several thousands of records was indexed quick, but
after that the speed reduces dramatically - if more records are
processed, the speed is more slower, and so on.
At the end each 1000 records were indexed in more 20 mins - which is
NON-SENS.

Does anybody know if in jBase 4.1 this problem is solved ?
Our version is 4.0.6.

Regards,
Alex.

[Clif Bristol]

Your right case 1 will take a long time but there is not point in having the index if you only have one or 2 distinct values. One of the purposes for an index is to have it distinguish between records but with only 2 values you defeat this purpose. With only a couple of values the index is of no value in sorting or selecting.

[Simon Verona]

Thank you for your email.

I am away from the office until 21/06/2005.

For urgent support issues, please contact your DMS Support consultant.

For other urgent issues, please contact the main DMS office on 0870 080 2300

Regards
Simon Verona
Dealer Management Services Ltd

This message was checked by MailScan for WorkgroupMail.
www.workgroupmail.com

[Atkinson, Chad]

The best way to avoid the slow build is to concatenate the value with
the item id using an * then when you select the index use "KEY-SELECT
filename with index = "value*]""
That way each index is unique but the size of each index is still small
and doesn't build really slow. Hope this helps.

I agree that this should have been optimized by jbase thus eliminating
this issue, but Jim/JBase couldn't think of everything...

[foxy]

Correct, this was just a particular case (an example if You wand :).

But my DB has (or will have) several millions of records with a field
that has 50-100 distinct values (its a department code).
In this case indexing is also too slow. But in need this DB to be
indexed by this field to speed up the selection.
What should I do in this case ?

Alex.

[Clif Bristol]

In this case Chad's recommendation will work by concatenating the item id onto it.

-----Original Message-----
From: jB...@googlegroups.com [mailto:jB...@googlegroups.com]On Behalf Of
foxy

[Simon Verona]

[Simon Verona]

[foxy]

Chad,

thanks for the idea.
But unfortunatelly I need this in Temenos Globus environemnt (banking
system) and it uses SELECT instead of KEY-SELECT.

Alex.

[Charlie Noah]

Alex,

As mentioned before, you can append some value from the database to the
key, so that you have many thousands (or tens of thousands) of keys,
instead of just 50-100. The goal here is to have relatively small index
nodes. Let's say that you normally sort the file by department by name.
Create an index of dept * name, and use this select:

KEY-SELECT file WITH DEPT.NAME = "nn*]"

The index will build quickly, and you will have your list already sorted
(a side benefit if this works for you).

This is a characteristic of indexes and not unique to Jbase. It's just
the way it is.

Regards,
Charlie Noah
Inland Truck Parts

[foxy]

Charlie,

as I remember in Oracle I didn't have such problem.
Regardless of the distinct values, the indexing process took same time.
It depeds just of the DB size.

May be I'm wrong, it's not me who was the admin of the Oracle DB, but
this is what I know.

alex.

[Simon Verona]

[Kevin Powick]

> -----Original Message-----
> From: jB...@googlegroups.com [mailto:jB...@googlegroups.com]On Behalf Of
> Simon Verona
> Sent: June 14, 2005 11:01 AM
> To: jB...@googlegroups.com

> Subject: Re: Re: CREATE-INDEX issue
>
>
>
> Thank you for your email.
>
> I am away from the office until 21/06/2005.

So, we're going to continue to see these auto-replys until then?

<Sigh> Simon, Simon, Simon.

---
Kevin Powick

[John Lambert]

The problem is that the jBASE implementation of duplicated key values is
somewhat less efficient than it could be. The work around is to avoid
having duplicate keys by adding the item ID to every key, thereby
forcing each node to be unique. Do this by adding BY 0 to the end of the
index spec rather than creating a composite concatenated value, this
should allow jQL to use the index and ignore the item ID part of the
key.

John Lambert

-----Original Message-----
From: jB...@googlegroups.com [mailto:jB...@googlegroups.com] On Behalf

Of Clif Bristol
Sent: Tuesday, June 14, 2005 7:49 AM
To: jB...@googlegroups.com
Subject: RE: CREATE-INDEX issue

In this case Chad's recommendation will work by concatenating the item
id onto it.

-----Original Message-----
From: jB...@googlegroups.com [mailto:jB...@googlegroups.com]On Behalf Of

foxy
Sent: Tuesday, June 14, 2005 7:44 AM
To: jB...@googlegroups.com

[Simon Verona]

Thank you for your email.

I am away from the office until 21/06/2005.

[Danny Ruckel]

Alex,

If you are doing a binary search/sort against 1 million unique index
keys, you effectively cut your sample in half with each compare. At
most with this scenario, you have to do ~ 20 compares.

If you are doing a binary search/sort against 1 million "True/False"
index keys, for arguments sake, 50% true, 50% false, you can cut the
number of index keys to compare in half with your first compare, but
then, you'll have to test 500,000 times afterwards.

Regardless of the DBMS/OS, the second scenario is going to take a bit
longer...

804 Las Cimas Parkway, Suite 200
Austin, TX 78746
Voice: (512)278-5359

-----Original Message-----
From: jB...@googlegroups.com [mailto:jB...@googlegroups.com] On Behalf
Of foxy
Sent: Tuesday, June 14, 2005 10:05 AM
To: jB...@googlegroups.com
Subject: Re: CREATE-INDEX issue

Charlie,

alex.

Notice: This transmission is for the sole use of the intended recipient(s) and may contain information that is confidential and/or privileged. If you are not the intended recipient, please delete this transmission and any attachments and notify the sender by return email immediately. Any unauthorized review, use, disclosure or distribution is prohibited.

[Simon Verona]

[Simon Verona]

[foxy]

Danny,

scenario can be another - all depends of the algorithm of building
index.

For example, in your case with 1,000,000 records with true/false values
: why should I compare all the 500,000 values ? If I found the first
equal to mine - that's it - I put it in the same leaf. (if binary
search (greater or less) is used).

As I sad - all depends on the algorithm.

Alex.

[foxy]

UNTIL 21/06/2005 :))))))

[T.Turkington]

Thank god (or google) his email client isn't responding to his own responses.

Tom

-----Original Message-----
From: jB...@googlegroups.com [mailto:jB...@googlegroups.com]On Behalf Of

Kevin Powick
Sent: Tuesday, June 14, 2005 8:13 AM
To: jB...@googlegroups.com

[Simon Verona]

Thank you for your email.

I am away from the office until 21/06/2005.

[Jim Idle]

> -----Original Message-----
> From: jB...@googlegroups.com [mailto:jB...@googlegroups.com] On Behalf
Of

> I agree that this should have been optimized by jbase thus eliminating
> this issue, but Jim/JBase couldn't think of everything...

Oy! I did think of this, but some fat bloke who shall remain nameless,
but whose first name begins with Greg and whose last name is similar to
C**per didn't get around to fixing it.

What is actually happening is that the ID is being appended lots of
times to the same key. When you retrieve the IDs for a particular key,
this makes it really fast, but the way it is built, the build is quite
slow.

As Chad said (and this was stolen from me too! ;-), the way around this
is to build the index keys incorporating the item ID. The downside is
that there is no amalgamation of IDs into keys at all as they will
always be unique. This means retrieval is a bit slower (though you won't
notice it too much to be honest).

However indexes always take some consideration. Though an index on a
field that is Y or N only seems at first hand to be useless, there are
cases where it might be reasonable. There are other index structures
which can optimize such things, but they are not in jBASE as it stands.

So, before just building an index on a field, take a little time to
analyze the data and do a little experimentation. But cascading
KEY-SELECTs will help your performance enormously if you can use them.

What should happen though is that the build index should be revamped -
as neither Greg, John or myself work there though, this is not likely to
happen soon (though I think TEMENOS have commented favorably about doing
this in the past.)

Actually, what "really" really should happen is that the indexes should
be replaced with TRIE structures and then the whole issue would go away
(though index build should still be optimized.

Jim

[Jim Idle]

Unless you are using version 4.1 of jBASE, the SELECT statement will
make poor use of indexes (only in limited cases). If you are unable to
change to use KEY-SELECT directly, your only choice is to ask TEMENOS to
change to implement indexes.

Well, there is one other choice and that it is to intercept the SELECT
statement and analyze it yourself, but this is non-trivial.

Jim

> -----Original Message-----
> From: jB...@googlegroups.com [mailto:jB...@googlegroups.com] On Behalf
Of

> foxy
> Sent: Tuesday, June 14, 2005 7:56 AM
> To: jB...@googlegroups.com

[Jim Idle]

Oracle will build indexes using a different internal format - it will
then optimize the index structure. jBASE just has the one structure
which was written for optimal retrieval of multi-valued structures. This
has a down side on the initial build of the index though.

If a fat bloke called Greg is listening, I can tell him how to write a
new build-index command that will get around the initial build issues.
Individual writes changing the index do not affect performance enough to
worry too much. This would be an offline build only though I suggest.

Perhaps, if he wrote such a thing, you would like to buy a copy of it
from him?

Jim

Greg: hint - write a new index builder to do it in memory in big chunks
and stuff, building the leaves only, then build the index structure at
the end - Bob's your uncle.

> -----Original Message-----
> From: jB...@googlegroups.com [mailto:jB...@googlegroups.com] On Behalf
Of
> foxy
> Sent: Tuesday, June 14, 2005 8:05 AM
> To: jB...@googlegroups.com
> Subject: Re: CREATE-INDEX issue
>
>

[Jim Idle]

> -----Original Message-----
> From: jB...@googlegroups.com [mailto:jB...@googlegroups.com] On Behalf
Of

> by adding BY 0 to the end of the
> index spec rather than creating a composite concatenated value, this
> should allow jQL to use the index and ignore the item ID part of the
> key.

I believe that this is only the case for jQL 4.1 though John, not 3.x or
4.0.

Jim

[foxy]

Jim Idle wrote:
> Unless you are using version 4.1 of jBASE, the SELECT statement will
> make poor use of indexes (only in limited cases). If you are unable to
> change to use KEY-SELECT directly, your only choice is to ask TEMENOS to
> change to implement indexes.

:) I think this is impossble to ask TEMENOS to do something.
Did You succeeded ? ;)

> Well, there is one other choice and that it is to intercept the SELECT
> statement and analyze it yourself, but this is non-trivial.

Any idea how to intercept SELECT statement ?

Alex.

[foxy]

Correct. I have added BY 0 to the end and there were no effect in
SELECTs.
Hope in 4.1 will be OK.

Alex.

[Jim Idle]

> -----Original Message-----
> From: jB...@googlegroups.com [mailto:jB...@googlegroups.com] On Behalf
Of

> foxy
>
> :) I think this is impossble to ask TEMENOS to do something.
> Did You succeeded ? ;)

Yes - but only when I worked there ;-)

> > Well, there is one other choice and that it is to intercept the
SELECT
> > statement and analyze it yourself, but this is non-trivial.
>
> Any idea how to intercept SELECT statement ?

Yes. Description below is for 3.x/4.0 only - you won't need to know what
happens in 4.1.

The SELECT statement and other jQL statements are just executables in
/usr/jbc/bin. Therefore if you EXECUTE "SELECT x x x x", a few things
happen:

1) A short examination of the statement is performed by the internal
libraries to see if it is trivial enough to be executed by a cut-down
version of jQL that resides in the libraries. This is for statements
such as "SELECT FILE" or a simple SELECT FILE WITH *A1="Y".
2) Assuming that the select is not simple enough for the internal
library it calls the SELECT executable, which is essentially a symbolic
link to the executable ENQUIRE (copy of this on Windows and suffixed
with .EXE). However, I believe that it does this using a FQP and not the
$PATH, so I don't think you can intercept it by putting your own
SELECT.exe before /usr/jbc/bin in the $PATH (but you could try it
first).

Therefore, you would need to move /usr/jbc/bin/SELECT to say
/usr/jbc/bin/jbase and install your own SELECT executable (need to also
cater for lower case "select", here). This can be a BASIC program, and
it will receive, I believe, the statement in the SENTENCE() function and
@SENTENCE variable.

From here you can analyze the file and dictionary elements and decide if
you could pre-select anything using KEY-SELECT, before passing on the
statement to SELECT via EXECUTE "/usr/jbc/bin/jBASE/SELECT ":statement.

Now be careful here because this is not for the feint of heart. Parsing
the FILE to find the correct one, catering for MACROS in the VOC/MD and
finding the correct dictionary elements can be really tricky. Start with
something real simple such as an absolute string match, rather than
parse the statement. For instance:

BEGIN CASE
CASE @SENTENCE[1, 26] = "SELECT ACCOUNTS WITH NAME=" THEN

* Pick out the name qualifier
*
Name = blah balh

* Pre-select using an index, which makes the rest faster
*
EXECUTE "KEY-SELECT ACCOUNTS NAME=:"Name RTNLIST mylist
EXECUTE "/usr/jbc/jbase/bin/":@SENTENCE

CASE 1

EXECUTE "/usr/jbc/jbase/bin/":@SENTENCE
END CASE


Unless you really know what you are doing here, I would strongly suggest
that you hire Greg Cooper for a week or so and let him do it for you. He
knows jBASE inside out as he wrote it with me, and he knows a fair bit
about how Globus works on it too. Let me know if you would like his
contact info.

Jim

[Jim Idle]

> -----Original Message-----
> From: jB...@googlegroups.com [mailto:jB...@googlegroups.com] On Behalf
Of
> foxy

> Correct. I have added BY 0 to the end and there were no effect in
> SELECTs.
> Hope in 4.1 will be OK.

4.1 is really good at jQL statements and will make good use of indexes.
It is reason enough on its own to move. However, it is a huge change
internally so it will need lots of testing for any application.

Jim

[foxy]

> > :) I think this is impossble to ask TEMENOS to do something.
> > Did You succeeded ? ;)
>
> Yes - but only when I worked there ;-)

:))) Ha-ha-ha. That one was good ;)

> > Any idea how to intercept SELECT statement ?
>
> Yes. Description below is for 3.x/4.0 only - you won't need to know what
> happens in 4.1.
>

[...]
Thank You very much for info.

Alex.

[Charlie Noah]

Has anyone run 2 completely separate iterations of Jbase
on the same Unix machine? My particular interest would be
in having a development and testing environment, each
totally unaware of the other.

Thanks,

[Lee H. Burstein]

Charlie,

You could set up a totally separate login with its own home directory and
environment variable settings specific to your development environment. For
instance: JBCRELEASEDIR and JBCGLOBALDIR would point to the appropriate
jBASE directories but application specific settings like PATH,
JBCOBJECTLIST, JEDIFILEPATH, etc. would look at your development directories
and not your production ones.


Lee H. Burstein
President
Dynamic Systems, Inc.
302-477-0180
www.dynamicsys.com

-----Original Message-----
From: jB...@googlegroups.com [mailto:jB...@googlegroups.com] On Behalf Of

[Charlie Noah]

Lee,

What about starting up Jbase? How would you have 2 jPML and jRLA processes
running, with each only handling the proper logins? Forgive me if this is
a no-brainer question, but the light hasn't come on yet.

Thanks,
Charlie

[Doug Chanco]

Charlie,
What I think Lee is referring to is using environmental variables to
point to two different databases ........

For example:

JBCDEV_LIB
JBCDEV_BIN

Which points to where to put executables/libraries would be pointing to
(for Development)

JBCDEV_LIB=/ult/development/lib
JBCDEV_BIN=/ult/development/bin

And for "live"

JBCDEV_LIB=/ult/live/lib
JBCDEV_BIN=/ult/live/bin

Then you the profile for user dev would have

export JBCDEV_LIB=/ult/development/lib
export JBCDEV_BIN=/ult/development/bin

and user live would have

export JBCDEV_LIB=/ult/live/lib
export JBCDEV_BIN=/ult/live/bin

the same would apply to JEDIFILEPATH

JEDIFILEPATH=/ult/development/data

And

JEDIFILEPATH=/ult/live/data

And the profiles would be modified accordingly

The same can be done with other jBase env. variables .......

JBCOBJECTLIST, JBASETMP, JEDIFILENAME_MD, etc for example
This will isolate the two accounts from each other

Of course you would need to have separate users with separate home
directories, separate .profiles ,etc .....

If you need any help with this let me know and you have helped me often
enough, and I certainly owe you .....


Dougc

_________________________________
Doug Chanco
Engineer Developer Senior, TLOTLD

Activant Solutions Inc.(tm)
Austin, Texas 78746
T: 800-678-5266
F: 512-278-5915
E-mail: doug....@activant.com
Web site: www.activant.com
_________________________________

[Lee H. Burstein]

Charlie,

I believe that jPML and jRLA would be running system wide for all jBASE
users regardless of where your jStuff is. So starting you first jProgram
(jsh or your own executable or jbcconnect) will use the current instance of
jPML and your locks will use jRLA.

Lee H. Burstein
President
Dynamic Systems, Inc.
302-477-0180
www.dynamicsys.com

-----Original Message-----
From: jB...@googlegroups.com [mailto:jB...@googlegroups.com] On Behalf Of
Charlie Noah

Sent: Thursday, September 01, 2005 4:44 PM
To: jB...@googlegroups.com

[j...@cexp.com]

Charlie

I don't think it is possible to run multiple instances of jPML on the same
UNIX machine. If you attempt to start a second instance of jPML you will get
an error something like:

jPML: Unable to create IPC message queue
jPML: msgget(): File exists

AFAIK, there is no way tell jPML to create another set of message queues.

A non-trivial approach (at least on AIX) is to divide your machines into
multiple logical partitions (LPARs). We have done this on AIX 5.3 with jBASE
4. This approach gives you what you want, and then some as you effectively
have multiple instances of the OS. Bear in mind that logical partitioning
requires AIX 5.1 or above. For more info:
http://www-03.ibm.com/servers/eserver/pseries/lpar/faq_4.html

Regards
Jim Young

[Greg Cooper]

You can probably have more chance of success with jBASE 4.1.x as this release stops using jPML. Now you only have to worry about a single instance of jRLA, but the use of jRLA isn't mandatory so you can either leave jRLA alone (and hence use OS locks) or have a single instance of jRLA which is the only commonality between the systems.

 

Having said that, on jBASE 4.1.x there is now the license server to consider, AFAIK you can only have one instance of that. One step forward, two backwards ....

j...@cexp.com wrote:

[Daniel Klein]

Actually there's no reason to take any steps backwards ;-)

 

You can run a second instance by changing the port numbers, which are configurable in .properties files, on which the jLicServer and jSlimServer processes listen.

 

Then, as GC said, just use OS locks for one and jRLA locks for the other and your good to go.

 

Dan

[Charlie Noah]

Thanks to all who responded. We will probably stay as we are right now. We haven't
the time or psychic energy to go through a port to 4.1, and can identify users
accounts by their lognames sufficiently for our purposes.

Regards,
Charlie Noah

Daniel Klein wrote:
> Actually there's no reason to take any steps backwards ;-)
>
> You can run a second instance by changing the port numbers, which are
> configurable in .properties files, on which the jLicServer and
> jSlimServer processes listen.
>
> Then, as GC said, just use OS locks for one and jRLA locks for the other
> and your good to go.
>
> Dan
>
> -----Original Message-----

> *From:* jB...@googlegroups.com [mailto:jB...@googlegroups.com] *On
> Behalf Of *Greg Cooper
> *Sent:* Thursday, September 01, 2005 18:15
> *To:* jB...@googlegroups.com
> *Subject:* RE: SPAM-LOW: Re: Multiple Jbases Running

>
> You can probably have more chance of success with jBASE 4.1.x as this
> release stops using jPML. Now you only have to worry about a single
> instance of jRLA, but the use of jRLA isn't mandatory so you can either
> leave jRLA alone (and hence use OS locks) or have a single instance of
> jRLA which is the only commonality between the systems.
>
> Having said that, on jBASE 4.1.x there is now the license server to
> consider, AFAIK you can only have one instance of that. One step
> forward, two backwards ....
>