[Progress Communities] [Progress OpenEdge ABL] Forum Post: RE: Question about the performance of ABL queries resolved by multiple indexes

Status
Not open for further replies.
D

dbeavon

Guest
Thanks for the help. I had to look up the definition of Rought. ;) The slow example that you described was exactly what I was expecting to see when using three independent indexes with the year (fy) week (period) and company (div). By using all three indexes to resolve the query, I would have expected it to be slow since each of the criteria individually returned a million rows. Perhaps a million is not all that much when it comes to reading index pages and hash-joining ROWID's. (Do you happen to know the approximate numbers of rows in your own example)? Also just for clarification, you talk about "reading 98% of the data"... but you mean it is scanning the ROWID's in the index *pages*. I am assuming that even in your example the query doesn't gather data from the database table itself until after it has identified the subset of data that is applicable to *both* criteria. Another thing that I'd mention is our our memory situation. Even if the approach for resolving the query is terrible, it might be obscured by the tuning of our buffers and by the nature of when/how the queries are executed. For example we use alternate buffers for this table and I think the related index pages are almost always in RAM. Whereas in your example it is possible that you are overflowing your memory buffers and swapping on disk every time the query runs. It's just a guess. Our query may be running in a way that doesn't make any sense, but the availability of memory is compensating for that. Either way, I think the only "safe" way forward is to review the xrefs and individually force each of these instances to work differently. I suspect I will move them to another index via the USE-INDEX hint. Once all the code is weaned off from those three silly indexes, then I can drop them. (The only one of them that actually makes any sense to keep is the single column index on year... but we already have a handful of other indexes with year at the top anyway!)

Continue reading...
 
Status
Not open for further replies.
Top