Rocket U2 | UniVerse & UniData

We have a file that is used to find customers via a name & address formatted "matchcode".  This matchcode file has an index for customer number, as it has 38M entries.  For example:

MATCHCODE:     4537100110WSTLM00000BSHT
0001 59938613
0002 58926904

Often, there are more than one customer number (attributed) associated with a matchcode as shown in this example.  It seems that only <1> is indexed and not the others (.  Is this a function of how UniVerse indexes, or the dictionary value used or is it a function of how the index was created?

Interested in suggestions to handle this.

Nelson

We have a file that is used to find customers via a name & address formatted "matchcode".  This matchcode file has an index for customer number, as it has 38M entries.  For example:

MATCHCODE:     4537100110WSTLM00000BSHT
0001 59938613
0002 58926904

Often, there are more than one customer number (attributed) associated with a matchcode as shown in this example.  It seems that only <1> is indexed and not the others (.  Is this a function of how UniVerse indexes, or the dictionary value used or is it a function of how the index was created?

Interested in suggestions to handle this.

Nelson

We have a file that is used to find customers via a name & address formatted "matchcode".  This matchcode file has an index for customer number, as it has 38M entries.  For example:

MATCHCODE:     4537100110WSTLM00000BSHT
0001 59938613
0002 58926904

Often, there are more than one customer number (attributed) associated with a matchcode as shown in this example.  It seems that only <1> is indexed and not the others (.  Is this a function of how UniVerse indexes, or the dictionary value used or is it a function of how the index was created?

Interested in suggestions to handle this.

Nelson

We have a file that is used to find customers via a name & address formatted "matchcode".  This matchcode file has an index for customer number, as it has 38M entries.  For example:

MATCHCODE:     4537100110WSTLM00000BSHT
0001 59938613
0002 58926904

Often, there are more than one customer number (attributed) associated with a matchcode as shown in this example.  It seems that only <1> is indexed and not the others (.  Is this a function of how UniVerse indexes, or the dictionary value used or is it a function of how the index was created?

Interested in suggestions to handle this.

Nelson

Nelson,

You obviously already have the logic for calculating MATCHCODE. If the value is stored in the customer record, you can simply follow Manu's suggestion of an index on that field. You could incorporate a simple match code into an I-Descriptor and build an index on that dictionary. You can access a complex match code calculation using an @SUBR call to extant BASIC code in an I-Descriptor.

An advantage of the index on the customer file is that it is not subject to omission for a MATCHCODE update if whatever it is based upon may change (program logic issues likely resolved after 38M entries). It also pushes the calculation closer to the database engine for better efficiency. The downside is that the index will evaluate twice for each customer record update to compare before and after values for a possible index change. If match code is an "assign once at creation and remove upon deletion" calculation, an index on the customer adds overhead for each update.

Following the suggestions of Neil and Jonathan would likely minimize the perturbations in the application code.

We have a file that is used to find customers via a name & address formatted "matchcode".  This matchcode file has an index for customer number, as it has 38M entries.  For example:

MATCHCODE:     4537100110WSTLM00000BSHT
0001 59938613
0002 58926904

Often, there are more than one customer number (attributed) associated with a matchcode as shown in this example.  It seems that only <1> is indexed and not the others (.  Is this a function of how UniVerse indexes, or the dictionary value used or is it a function of how the index was created?

Interested in suggestions to handle this.

Nelson

Thanks to all the responses.  Very helpful.

I have tested the indexing with all values in attribute 1 but multivalued.  I have tested the indexing with values attributed, but using an I-descriptor LOWER(@RECORD).

Both work as expected.

Considering that our Matchcode file has 37M entries, and many of them have multiple cust# attributes, I am concerned that building the index by either of these two methods "could" create an index with well over 100M entries.  What limits might exist on such an index and what performance impacts might such a large index make on performance.  We need to understand this before determining if an index is worth having.

Nelson

Nelson,

You obviously already have the logic for calculating MATCHCODE. If the value is stored in the customer record, you can simply follow Manu's suggestion of an index on that field. You could incorporate a simple match code into an I-Descriptor and build an index on that dictionary. You can access a complex match code calculation using an @SUBR call to extant BASIC code in an I-Descriptor.

An advantage of the index on the customer file is that it is not subject to omission for a MATCHCODE update if whatever it is based upon may change (program logic issues likely resolved after 38M entries). It also pushes the calculation closer to the database engine for better efficiency. The downside is that the index will evaluate twice for each customer record update to compare before and after values for a possible index change. If match code is an "assign once at creation and remove upon deletion" calculation, an index on the customer adds overhead for each update.

Following the suggestions of Neil and Jonathan would likely minimize the perturbations in the application code.

We have a file that is used to find customers via a name & address formatted "matchcode".  This matchcode file has an index for customer number, as it has 38M entries.  For example:

MATCHCODE:     4537100110WSTLM00000BSHT
0001 59938613
0002 58926904

Often, there are more than one customer number (attributed) associated with a matchcode as shown in this example.  It seems that only <1> is indexed and not the others (.  Is this a function of how UniVerse indexes, or the dictionary value used or is it a function of how the index was created?

Interested in suggestions to handle this.

Nelson

Thanks to all the responses.  Very helpful.

I have tested the indexing with all values in attribute 1 but multivalued.  I have tested the indexing with values attributed, but using an I-descriptor LOWER(@RECORD).

Both work as expected.

Considering that our Matchcode file has 37M entries, and many of them have multiple cust# attributes, I am concerned that building the index by either of these two methods "could" create an index with well over 100M entries.  What limits might exist on such an index and what performance impacts might such a large index make on performance.  We need to understand this before determining if an index is worth having.

Nelson

Nelson,

You obviously already have the logic for calculating MATCHCODE. If the value is stored in the customer record, you can simply follow Manu's suggestion of an index on that field. You could incorporate a simple match code into an I-Descriptor and build an index on that dictionary. You can access a complex match code calculation using an @SUBR call to extant BASIC code in an I-Descriptor.

An advantage of the index on the customer file is that it is not subject to omission for a MATCHCODE update if whatever it is based upon may change (program logic issues likely resolved after 38M entries). It also pushes the calculation closer to the database engine for better efficiency. The downside is that the index will evaluate twice for each customer record update to compare before and after values for a possible index change. If match code is an "assign once at creation and remove upon deletion" calculation, an index on the customer adds overhead for each update.

Following the suggestions of Neil and Jonathan would likely minimize the perturbations in the application code.

We have a file that is used to find customers via a name & address formatted "matchcode".  This matchcode file has an index for customer number, as it has 38M entries.  For example:

MATCHCODE:     4537100110WSTLM00000BSHT
0001 59938613
0002 58926904

Often, there are more than one customer number (attributed) associated with a matchcode as shown in this example.  It seems that only <1> is indexed and not the others (.  Is this a function of how UniVerse indexes, or the dictionary value used or is it a function of how the index was created?

Interested in suggestions to handle this.

Nelson

If a match code can have multiple customers, the customer probably has, at most, one match code. That would suggest the number of match codes is limited to the number of customers.

Thanks to all the responses.  Very helpful.

I have tested the indexing with all values in attribute 1 but multivalued.  I have tested the indexing with values attributed, but using an I-descriptor LOWER(@RECORD).

Both work as expected.

Considering that our Matchcode file has 37M entries, and many of them have multiple cust# attributes, I am concerned that building the index by either of these two methods "could" create an index with well over 100M entries.  What limits might exist on such an index and what performance impacts might such a large index make on performance.  We need to understand this before determining if an index is worth having.

Nelson

Nelson,

You obviously already have the logic for calculating MATCHCODE. If the value is stored in the customer record, you can simply follow Manu's suggestion of an index on that field. You could incorporate a simple match code into an I-Descriptor and build an index on that dictionary. You can access a complex match code calculation using an @SUBR call to extant BASIC code in an I-Descriptor.

An advantage of the index on the customer file is that it is not subject to omission for a MATCHCODE update if whatever it is based upon may change (program logic issues likely resolved after 38M entries). It also pushes the calculation closer to the database engine for better efficiency. The downside is that the index will evaluate twice for each customer record update to compare before and after values for a possible index change. If match code is an "assign once at creation and remove upon deletion" calculation, an index on the customer adds overhead for each update.

Following the suggestions of Neil and Jonathan would likely minimize the perturbations in the application code.

We have a file that is used to find customers via a name & address formatted "matchcode".  This matchcode file has an index for customer number, as it has 38M entries.  For example:

MATCHCODE:     4537100110WSTLM00000BSHT
0001 59938613
0002 58926904

Often, there are more than one customer number (attributed) associated with a matchcode as shown in this example.  It seems that only <1> is indexed and not the others (.  Is this a function of how UniVerse indexes, or the dictionary value used or is it a function of how the index was created?

Interested in suggestions to handle this.

Nelson

Thanks for all the valuable responses.

I built an index using the LOWER(@RECORD) dict itme.  it took anly about 1 hour for 37M matchcodes even using the concurrent option.  However, the disk hit was enormous as shown below.  Selects come back immediately, so it works nicely.  Now we have to decide if this index has appropriate usage in our ERP :-)

If a match code can have multiple customers, the customer probably has, at most, one match code. That would suggest the number of match codes is limited to the number of customers.

Thanks to all the responses.  Very helpful.

I have tested the indexing with all values in attribute 1 but multivalued.  I have tested the indexing with values attributed, but using an I-descriptor LOWER(@RECORD).

Both work as expected.

Considering that our Matchcode file has 37M entries, and many of them have multiple cust# attributes, I am concerned that building the index by either of these two methods "could" create an index with well over 100M entries.  What limits might exist on such an index and what performance impacts might such a large index make on performance.  We need to understand this before determining if an index is worth having.

Nelson

Nelson,

You obviously already have the logic for calculating MATCHCODE. If the value is stored in the customer record, you can simply follow Manu's suggestion of an index on that field. You could incorporate a simple match code into an I-Descriptor and build an index on that dictionary. You can access a complex match code calculation using an @SUBR call to extant BASIC code in an I-Descriptor.

An advantage of the index on the customer file is that it is not subject to omission for a MATCHCODE update if whatever it is based upon may change (program logic issues likely resolved after 38M entries). It also pushes the calculation closer to the database engine for better efficiency. The downside is that the index will evaluate twice for each customer record update to compare before and after values for a possible index change. If match code is an "assign once at creation and remove upon deletion" calculation, an index on the customer adds overhead for each update.

Following the suggestions of Neil and Jonathan would likely minimize the perturbations in the application code.

We have a file that is used to find customers via a name & address formatted "matchcode".  This matchcode file has an index for customer number, as it has 38M entries.  For example:

MATCHCODE:     4537100110WSTLM00000BSHT
0001 59938613
0002 58926904

Often, there are more than one customer number (attributed) associated with a matchcode as shown in this example.  It seems that only <1> is indexed and not the others (.  Is this a function of how UniVerse indexes, or the dictionary value used or is it a function of how the index was created?

Interested in suggestions to handle this.

Nelson

Hi Nelson

Nice to read you have good perf. 

Anyway, I do not understand you big file MCODE !

If I understand correctly, you build it from CUST file via a itype which concat some fields and put CUSTid as record values.

Why not build the index directly like this

CREATE.INDEX CUST MCODE NO. NULLS

BUILD. INDEX CUST MCODE

You get the same result and nothing to do for maintenance of index, it's automatic! And one 64bit file less. 

With kind regards. 

Thanks for all the valuable responses.

I built an index using the LOWER(@RECORD) dict itme.  it took anly about 1 hour for 37M matchcodes even using the concurrent option.  However, the disk hit was enormous as shown below.  Selects come back immediately, so it works nicely.  Now we have to decide if this index has appropriate usage in our ERP :-)

If a match code can have multiple customers, the customer probably has, at most, one match code. That would suggest the number of match codes is limited to the number of customers.

Thanks to all the responses.  Very helpful.

I have tested the indexing with all values in attribute 1 but multivalued.  I have tested the indexing with values attributed, but using an I-descriptor LOWER(@RECORD).

Both work as expected.

Considering that our Matchcode file has 37M entries, and many of them have multiple cust# attributes, I am concerned that building the index by either of these two methods "could" create an index with well over 100M entries.  What limits might exist on such an index and what performance impacts might such a large index make on performance.  We need to understand this before determining if an index is worth having.

Nelson

Nelson,

You obviously already have the logic for calculating MATCHCODE. If the value is stored in the customer record, you can simply follow Manu's suggestion of an index on that field. You could incorporate a simple match code into an I-Descriptor and build an index on that dictionary. You can access a complex match code calculation using an @SUBR call to extant BASIC code in an I-Descriptor.

An advantage of the index on the customer file is that it is not subject to omission for a MATCHCODE update if whatever it is based upon may change (program logic issues likely resolved after 38M entries). It also pushes the calculation closer to the database engine for better efficiency. The downside is that the index will evaluate twice for each customer record update to compare before and after values for a possible index change. If match code is an "assign once at creation and remove upon deletion" calculation, an index on the customer adds overhead for each update.

Following the suggestions of Neil and Jonathan would likely minimize the perturbations in the application code.

We have a file that is used to find customers via a name & address formatted "matchcode".  This matchcode file has an index for customer number, as it has 38M entries.  For example:

MATCHCODE:     4537100110WSTLM00000BSHT
0001 59938613
0002 58926904

Often, there are more than one customer number (attributed) associated with a matchcode as shown in this example.  It seems that only <1> is indexed and not the others (.  Is this a function of how UniVerse indexes, or the dictionary value used or is it a function of how the index was created?

Interested in suggestions to handle this.

Nelson

Let me explain better.

• The MATCHCODE is built via a subroutine based upon the Name, Address, etc. of the customer.
• the MATCHCODE file has 1-n matching customer numbers listed in it's attribute list
• the INDEX was built on the MATCHCODE file via an I-Descriptor LOWER(@RECORD) 
• There are over 37M MATCHCODE records, many of which have multiple customer numbers associated with it (for example, different family members at same address)

The information I provided was on this index.

Nelson

Hi Nelson

Nice to read you have good perf. 

Anyway, I do not understand you big file MCODE !

If I understand correctly, you build it from CUST file via a itype which concat some fields and put CUSTid as record values.

Why not build the index directly like this

CREATE.INDEX CUST MCODE NO. NULLS

BUILD. INDEX CUST MCODE

You get the same result and nothing to do for maintenance of index, it's automatic! And one 64bit file less. 

With kind regards. 

Thanks for all the valuable responses.

I built an index using the LOWER(@RECORD) dict itme.  it took anly about 1 hour for 37M matchcodes even using the concurrent option.  However, the disk hit was enormous as shown below.  Selects come back immediately, so it works nicely.  Now we have to decide if this index has appropriate usage in our ERP :-)

If a match code can have multiple customers, the customer probably has, at most, one match code. That would suggest the number of match codes is limited to the number of customers.

Thanks to all the responses.  Very helpful.

I have tested the indexing with all values in attribute 1 but multivalued.  I have tested the indexing with values attributed, but using an I-descriptor LOWER(@RECORD).

Both work as expected.

Considering that our Matchcode file has 37M entries, and many of them have multiple cust# attributes, I am concerned that building the index by either of these two methods "could" create an index with well over 100M entries.  What limits might exist on such an index and what performance impacts might such a large index make on performance.  We need to understand this before determining if an index is worth having.

Nelson

Nelson,

You obviously already have the logic for calculating MATCHCODE. If the value is stored in the customer record, you can simply follow Manu's suggestion of an index on that field. You could incorporate a simple match code into an I-Descriptor and build an index on that dictionary. You can access a complex match code calculation using an @SUBR call to extant BASIC code in an I-Descriptor.

An advantage of the index on the customer file is that it is not subject to omission for a MATCHCODE update if whatever it is based upon may change (program logic issues likely resolved after 38M entries). It also pushes the calculation closer to the database engine for better efficiency. The downside is that the index will evaluate twice for each customer record update to compare before and after values for a possible index change. If match code is an "assign once at creation and remove upon deletion" calculation, an index on the customer adds overhead for each update.

Following the suggestions of Neil and Jonathan would likely minimize the perturbations in the application code.

We have a file that is used to find customers via a name & address formatted "matchcode".  This matchcode file has an index for customer number, as it has 38M entries.  For example:

MATCHCODE:     4537100110WSTLM00000BSHT
0001 59938613
0002 58926904

Often, there are more than one customer number (attributed) associated with a matchcode as shown in this example.  It seems that only <1> is indexed and not the others (.  Is this a function of how UniVerse indexes, or the dictionary value used or is it a function of how the index was created?

Interested in suggestions to handle this.

Nelson