How Index Reads are More Than Table Reads

Hi All, hope you all are well!

I am trying to check table and index reads/writes for my queries by using "-tablerangesize, -indexrangesize" db startup parameters but index reads are displaying more then table reads.

Below is one small example:

Code:
def var icust  as int no-undo.                                                                                         
def var iord   as int no-undo.
def var iordln as int no-undo.                         

for each customer no-lock:
  icust = icust + 1. 
  for each order of customer no-lock:
    iord = iord + 1.
    for each order-line of order no-lock:
      iordln = iordln + 1.
    end.
  end.
end.
message "customer = " icust skip                                                                                   
               "Order = "    iord  skip
               "OrderLine = " iordln view-as alert-box.

Here, record reads are coming actual as 83,207 and 873 respectively but Index reads are coming as 84 (customer), 290 (Order) and 1080 (OrderLine). Unable to understand how index reads are more then table reads.

Regards,
 
Hi ,
It is OK that you read one index more than records on each part of your query
(the last one , to show it does match your index key)

83 records on customer => 84 read on index
207 order + 83 request => 290 read on index
873 orderline + 207 request => 1080 read on index.


If the read on index are above , I assume , you did some deletion on this table and you read the index entry lock (IEL , placeholder)

Kind regards

Patrice
 
Hi Patrice, thanks for your reply!

Though below calculation is absolutely correct but I am not able to understand how customer table reads are getting accumulated with order index reads, same for order and orderline.

83 records on customer => 84 read on index
207 order + 83 request => 290 read on index
873 orderline + 207 request => 1080 read on index.

Sorry, didn't get below explanation, could you please explain more on this (IEL):

If the read on index are above , I assume , you did some deletion on this table and you read the index entry lock (IEL , placeholder)

Best Regards
 
The request stops to read the index and the records when the value of this index does not respect the value of the index you specify
For example ;
for each order where custnum = 10 no-lock , the process will stop to read the data when it find an index with custnum > 10


for each customer no-lock: => 83 index read +1 index for no more record .
You run 83 times (1 by customer) "for each order of customer no-lock:" => 207 index order read + 83 index for no more record (on order for this specific value of customer).
you run 207 times (1 by order) "for each order-line of order no-lock:" = > 873 index orderline read + 207 index for no more record (on orderline for this specific value of order) .

IEL , placeholder :
During a transaction, when a record is deleted, if there is a unique index, a delete place holder is put in the place of the RECID for the deleted record until the transaction commits.
This is done so that another transaction running at the same time cannot add the same unique entry, which would thereby prevent the deleting transaction from rolling back.
At the end of the transaction, it is converted to a negative number and put it in place of the RECID in the index as a placeholder.
i.e. Progress treats the index entry locks as index entries during subsequent read operations.
The specified blocks are not read because RECID is higher than any possible High Water Mark (HWM) for that Area. But it's still a logical request.
index entry locks are not removed at the end of transaction
index delete chains
 
You can also try this code ,
you will have no read on orderline but 100 on its index.
<code>def var ii as int no-undo.
def var iord as int no-undo.
do ii = 1 to 100 :
for each order no-lock where custnum < 0 :
iord = iord + 1.
end.
end.
message "Order = " iord
view-as alert-box.</code>
If you increase or decrease the number of loop it will change the number of read of the index.

Patrice
 
Top