Question Long Transaction Problem

[Rajat Sharma]

Thanks Tom, MadDBA,

I have found WHOLE-INDEX issue with FIND LAST statement (that we discussed yesterday) but I didn't understand that why we are creating one index for n number of fields?

For ex:

I have one table c-data having 25 fields and one index c-index. Index c-index contains 5 fields of table c-data. But why, we are creating a single index for many fields because only the first field in the index list (leading component) will do effective table scan (when used in where clause) and rest all will perform complete table scan (WHOLE-INDEX). Isn't it be more effective if we define four indexes for four fields (I know it wouldn't, but why)?

Is it a kind of composite primary index which is a combination of multiple fields ?

Please suggest.

Thanks & Regards!
Rajat.

 

[TheMadDBA]

Hello again

It doesn't use just the first field in a multi field index, you just have at least use the first one. For example if you have equality matches in the where for the 1st, 2nd and 3rd fields Progress will happily use the index to quickly bracket to just the records that have the matching values for fields 1,2 and 3.

It might be a unique key/constraint of some kind. We can't tell without knowing all of the details, including if it makes sense for your application or not.

FIND LAST can be very evil unless you are really, really, really careful and monitor which programs pick different indexes when you add new ones to the database.

I strongly suspect you are supposed to be using more of those fields in your where clause to actually find the proper records, but we can't tell you what the right thing to do without knowing a lot more about your application.

 

[Rajat Sharma]

Thanks for replying MadDBA!

I tried many example there and found that, whenever i use first index "leading component" in where clause then it will use proper indexing (checked xref output).

For ex:

These are the four fields of table c-data those are part of one index c-index (with same sequence):
1. c-num
2. c-name
3. c-lname
4. c-add

If i don't use c-num in my where clause then it will always do full table scan "WHOLE-INDEX".

FIND LAST c-data NO-lOCK
       WHERE c-data. name = l-num
        AND c-data.c-lname   = l-lname
        AND c-data.c-add       = l-add.

If i compile the above code with xref option then the xref output contains "WHOLE-INDEX" there but if i add c-num in where clause then it will perform effective indexing with proper index (checked xref output for that ).

I was also thinking that why we are using FIND LAST and searching the last record of the table in an equality matching where clause? should we avoid using FIND LAST?

Please suggest!

Thanks & Regards!
Rajat.

 

[TheMadDBA]

Right... like we have said before the absolute minimum requirement to use and index without reading every record is using the first (leading) component of that index. That doesn't mean that it is 100% correct, but the bare minimum requirements.

FIND FIRST is acceptable in certain circumstances, mostly when you just need to verify the existence of a certain set of records and you don't care which one you get and the index properly supports all of the fields in the where clause. Other than that it should be seriously avoided.

Why aren't you just adding c-num = <some variable> in the code?

 

[Rajat Sharma]

Yes, i was also thinking to add c-num in the existing where clause and i will double check if i could replace FIND LAST with FIND FIRST.

Thanks & Regards!
Rajat.

 

[TheMadDBA]

There is no difference to the risk with FIRST and LAST, they both have the same set of issues.

FIND LAST in that case was probably intended to find the last available record based on that index sorting order. Don't just change that without knowing what is going to happen. That is another reason why a lot of people dislike FIND FIRST/LAST.

 

[Rajat Sharma]

Yes, i will double check that (as suggested) before making any changes. One more thing that i asked earlier is, why we are creating one index to contain n number of fields. I think we do that when we want to treat multiple fields (4 in our case) as a composite primary key index.

Please suggest.

Thanks & Regards!
Rajat.

 

[TheMadDBA]

That is sometimes the case, but sometimes it is just to find the records with those specific columns faster. In most cases multi-component indexes work better than having 4 different indexes.

You would have to ask the person who added the index what they were thinking.