Will changing to Unique index be worth it?

[Abe Froman]

I have been finding old indices that are unique in practice but aren't set as unique. I'd like to know if there will be any performance gain or any other good reason to take the time to make these indices unique.

BTW, using Progress 8.

Here are some examples:

po_mstr

po_nbr_key (pu)
(1) po_nbr

po_type_key (u)
(2) po_type, po_nbr

po_vend_key (-)
(2) po_vend, po_nbr

po_buyer_key (-)
(3) po_buyer, po_vend, po_nbr

In this case, po_vend_key and po_buyer_key are unique only due to the field po_nbr. Should I make them unique? Would I save space and streamline the indexing process if I shortened every index down to one field and made all but the primary non-unique? What would I gain or lose in each case?

Here's a different case.

xxse

xxse_key (pua)
(3) xxse_mstr_ord_nbr, xxse_mstr_ord_sect, xxse_ord_seq_id

xxse_sonbr_det_key (-)
(2) xxse_so_nbr, xxse_det_line

xxse_sonbr_sod_key (-)
(2) xxse_so_nbr, xxse_sod_line

xxse_so_nbr_key (-)
(2) xxse_so_nbr, xxse_ord_seq_id

All the indices with 2 fields are unique in practice, but not actually labelled as unique. I am aware of validation bonuses to making them unique, but other validation is already in place. Are there any other gains? I would guess they'll be slightly faster and take up slightly more space? If so, what real benefit is gained by 'fixing' these indices?

Are there any big-picture ideas I can learn here? If an ancient index structure can be slightly improved, is it always worth it to make the change? Should I spend more effort finding places to shorten and join indices instead of making multiple ones unique?

 

[Simon Sweetman]

Abe,

I must admit that I have had very little gain in changing an index to unique.

Shortening an index may cause logic problems with programs. Eg. if a problem in the system doing something like:

find last po_mstr where
    po_vend = "A"
    use-index po_vend_key
    no-lock no-error.
assign
    max_A_ord = po_mstr.po_nbr when available po_mstr.

I really think more benifit can be gained spotting searches that are off index, performing frequent dump and re-loads of the db and monitoring buffer levels/cache hit ratios through promon.

Simon.

 

[Unregistered]

THe Abe Froman? The 'Sausage King' of Chicago?

 

[Norman Biggar]

Opinion in our office seems to be that, certainly in older versions of Progress, setting an index to unique would slow database performance.

This was because before writing the record, Progress would have to perform additional checks to verify the uniqueness of the index element(s).

I think you'd have to compare
The benefits of data integrity offered by uniqueness
against
Slowing of performance, especially if you are working on a heavily written table.

 

[WGW]

Simon: That would only be an issue if 'USE-INDEX' was used, which never is. And is a search off-index if it could be a unique index but is only a regular index?

Norman: We're not using an older version. We're using V8. And wouldn't the be an overall gain in performance with a unique index if records are searched and read more than they are written to?

Aside from performance factors, the added gain is having an accurate map of tables and how they relate. Quite often, especially in our complex system, the easiest way to find the quickest way to grab a table is to look at the indices. To find out if a certain combination of fields yields a unique record would be a major hassle without accurate and complete unique index information to guide me.

Somehow, this major gain for the programmer is not a priority here. I need to prove that performance would not suffer before DBAdmin will consider such an 'unnecessary' change.

 

[Samj]

Are any of the queries multi-bracket queries?

From KB16808:
Multi-bracket queries are used by the GET, FOR EACH and PRESELECT statements in version 7 and later. They provide functionality similar to that of index cursors, but can use multiple index brackets and multiple index cursors and provide better performance.

From KB21099:
When the WHERE clause uses AND or OR clauses and indexes are available for both sides of the AND or OR, more than one index can be used...more than one index will be used when all the components of each index are involved in equality matches, and the indexes are not unique.

Looks like you could lose some performance...

 

[WGW]

I'm pretty sure I understand what you mean, but could you be more specific just in case?

In which scenario of which example would I take a hit on performance? It's like I asked 10 questions and you answered with a single 'Yes'. :awink:

And refresh my memory please. Can I do multiple bracket queries on FIND or CAN-FIND statements? Is there another thread discussing the pros, cons, and limitations behind multiple bracket queries that I can read?