Should I create a new index?

[Abe Froman]

Version 9.1C17. Windows.

We're using a packaged banking software that offers little database structure to help reporting. I'd like to add some indices that boost reporting speed, but don't hurt the performance of the current software.

Everything drives off of one main table - lt-master.

The one index in question is
branch-key:
+ brch-entry
+ stage

It's not primary or unique.

I want to be able to do
FOR EACH lt-master WHERE stage = 60:

Should I:

A. Create an additional index with the single field 'stage' and leave the branch-key index intact.

B. Create an additional index with the single field 'stage' and remove stage from the branch-key index.

C. Do nothing. Both would cause problems

I am afraid to change the branch-key index for potential perfomance loss within the main program. If two single-field indices would work as well as a double-field index, then that could work.

I'd appreciate some advice.

 

[Dan Dragut]

Hi Abe,

I think that you don't need to create a new index, only if don't want to modify lots of programs.

You can rephrase using BEGINS:
FOR EACH lt-master WHERE brch-entry BEGINS '' AND stage = 60:

You can take a look at the notes of the BEGINS operator in the Progress help, they say that "BEGINS uses an index wherever possible".

You can check your program as it was and with the change by compiling using XREF or use XRefAnal.w by Matt Verrinder (http://www.peg.com/utilities.html), to check that the program is using the right index.

HTH,
Dan

 

[Simon Sweetman]

Dan, I would have to disagree with your response. Although using the BEGINS operator may cause the xref to show that you are using an index. I don't feel this will do anything to improve performance on the query.

Basically progress will still need to examine all records that begin with '' which is basically a full table scan.

Abe would be much better off creating an index for stage on his database; this shouldn't adversely affect performance of existing applications as progress typically selects the best index for a given query.

I wouldn't suggest removing stage from the branch-key index (Option B) because code that uses that index may rely on records appearing in stage order.

 

[MurrayH]

Just create a new index on stage only.

Murray

 

[Abe Froman]

Follow-up questions...

Thanks for your replies.

I wish someone could tell me why I shouldn't remove stage from the branch-key index. Wouldn't two single-field indices do the same job as a single two-key index? I thought the only difference would be in cases where UNIQUE was used (which this isn't).

Also, here's another question about whether I should add an index.

I was also thinking about adding a couple of date indices, but now I doubt they would be helpful. Most reporting is done over date ranges, which wouldn't use an index. But would an index like this be totally useless? Does having a date index serve any purpose besides aiding searches on a single (equality) date?

 

[MurrayH]

Ok lets have a look. You could have two single column indexes.

index1 - stage
index2 - branch-key

This will work and Progress will merge the two indexes when you use equality matches and some other type which escapes me at the moment. Now, why have an index like this:
index3 -branch-key, stage

2 reasons:
1. its faster (slightly) than two individual indexes
2. If you use index 2 above, and match only on branch-key the values will be returned in a random order of stage. If you use index 3, the values will be sorted by stage (either ascending or descending) as its the second part of the index.

I use both methods myself so its really up to ou. However, if you use index 3, there really isn't any point in keeping index2 as its already contained in index3. Go and check out the ProLint site for the rules on indexes http://www.global-shared.com/prolint/

Hope that helps

Muz

 

[Abe Froman]

So does that give a hint as to my question about a date index?

Would a date field only be useful as the last field in another index (assuming searches with dates are mostly done with inequalities)?

For instance, an index with stage then close-date could be helpful if the stage search is an equality since it would then be already sorted by close-date. Not much use for finding records, but it would help speed the sort. Am I correct?

So, a single-field date index would be useless if most searches would be inequalities. Correct again?

 

[MurrayH]

You are correct on the date front, its useful for sorting to add an entry into the index BUT it does add overhead for updates etc.

So, a single-field date index would be useless if most searches would be inequalities. Correct again?

No, because you can do >, <, >=, <= etc. What you should do is add the indexes,re-compile, switch on VSTs and see how often the index gets used - OR do an XREF compile and check in there how often the index is used.

Murray

 

[Abe Froman]

I don't understand what you mean here.

The primary use of an index is for record selection, and any inequality would prevent it from being used. So anything from < to >= would prevent the index from being used. And an unused to rarely-used index isn't worth the overhead.

Are you talking about its usefulness for sorting? Truthfully, I haven't looked into whether we need a date index for sorting. Most searches would look for a date range for record selection but might sort by app-number or loan-officer instead of date. Anyway, most of the inefficiency is in the initial record selection. Once you chop down your records from tens of thousands to a few hundred, sorting without an index isn't a big deal.

But wouldn't a single-field date index be useless for sorts unless you weren't using another index and you were searching every record? As you said, the date would only be useful for sorting as a last field in an existing index.

 

[MurrayH]

Why wouldn't an index be used for >= ?? It should be - it is on Oracle.

Murray

 

[Abe Froman]

Sorry, what I meant to talk about was ranges match instead of inequalities.

Is it still true that when Progress encounters a range match, it ceases bracketing data at the level of that field? Would that prevent multiple indexing or even prevent that index from being selected at all if there were a single index that Progress liked better?

I think I might be operating on some outdated information when it comes to indexing. Where can I find Index Selection Rules and an Index Rules Summary for v9? I'm not sure what was improved since v8.

The trouble is I used to work for a company with an older version of Progress. Now I'm at a place with v9. The types of searches are also very different at this new place, so rules I used to live by might no longer apply here.

I'm afraid to add an index that Progress will choose over a current index, even if the original index might be the best choice. Now I doubt that adding that single field 'stage' index might be so harmless after all. Progress might select that index due to the equality (ex. stage = 60) when the branch - stage index would've bracketed fewer records even though branch would be a range match.

I doubt everything now. I'm a gibbering mess on the subject. I can make a case for leaving the indices as they are and I can make a case for adding to them. Is making the change and seeing if the system blows up the only way I can be sure? It's hard to add an index to packaged software without knowing if there might be very specific reasons why that index wasn't added in the first place by the software company.

The new proposed date indices might be safer than the stage one, since they wouldn't overlap any existing index fields. Still...

Ugh. Time to go to lunch and rest my head.

 

[toby.Harman]

If I may offer an alternative point of view.

There is more than one way to skin a cat!

Assumption: we don't want to destroy the index as it stands because it is used by some other code somewhere else in the system which you may or may not be aware of.

Two choices exist.

1. As Murray and you have agreed - add the index on stage.

2. Run a double loop (bear with me) and use a temp table for sorting etc.

Assumption: there aren't that many unique branches.

try something like this:

def var l-brch-entry like lt.master.brch-entry no-undo initial "".
def buffer b1-lt-master for lt-master.
find first b1-lt-master
no-lock no-error
use-index branch-key.

do while available b1-lt-master:
for each lt-master
no-lock
where lt-master.brch-entry = b1-lt-master.brch-entry:

<Store relevant values into a temp table indexed how you want>

end.
l-brch-entry = b1-lt-master.brch-entry.
find first b1-lt-master
no-lock
where b1-lt-master.brch-entry > l-brch-entry
no-error
use-index branch-key.
end.

for each <temp-table>
report
report
report
end.

Now I know a number of purists are going to jump on my case about use of use-index, but in this case I believe it may be necessary.

Ultimately temp-tables are great for this kind of task.