[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
The problem for me is that the selectivity of each of these separate indexes is too high (a million rows for any criteria I might specify). And practically speaking, nobody is going to have a good reason to query on the week-of-year in isolation (without specifying the fiscal-year). Nor would anyone query the financial books for a given company without also picking the time period. Every feature like this comes at a price. You are compromising one thing to get another. Do I really want to allocate tons of RAM and CPU in order to maintain large buffers of index pages, and perform these massive hash-match joins on ROWID's? It does not seem like a conventional way to use the resources of the database server (OLTP). I think many customers would be licensed by CPU core, and the capacity that can be handled by a database server's CPU's may depend on the type of data joins as well as the number of indexes that are used to resolve a single query. >> Why do you think they are silly I was a bit prejudiced in the first place, but the main reason I came to the opinion was after reviewing the places where they show up in xrefs. In all the cases the query had to be written in a special way, and in all cases I would have actually preferred it to use another single index. And in all cases the query ran slower than a similar multi-field index. I would probably feel better about these types of indexes if Progress could pick them based on a dynamic, cost-based analysis (statistics-based). However I suspect that the costs would normally be higher, and it would be all the more reason not to use these over normal indexes.

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