>>>Okay, I see your point. But I'm still biased in favor of using a view *grin*
>>
>>Me too. For one, SET KEY has the limitation of being restricted to a single order. Unless I create lots of composite indices.
>
>Huh ?
>Now lets all grin; did we all evolute to better not have indexes at all ? If so, I should re-think what I'm doing here ;)
>One thing I know : any next question at UT about slow responses, we should reply with the question "are you possibly using vieuws ?".
>Hilmar, you can't be serious here.

OK, let me give an example. In a parent-child scenario, in the child table I want to use SET KEY to "filter" certain records. The filter condition in the child table - were I to use a filter - would be: set filter to parent = parent.parent. With SET KEY, the command would be something like: SET KEY TO parent.parent.

At the same time, the user also wants the results sorted on a certain field - or fields. Thus, I need an index on parent + orderfield1 + orderfield2, and then SET KEY TO parent. Now, I don't want to create an index on every possible combination of fields the user may want to see.

>>With a view, I can quickly retrieve a subset of data, and then index (done automatically by the framework, e.g., if the user double-clicks on the grid headers).
>
>Never thought of this one. Now please come overhere and implement this on all of our tables usually containing over 1M records. BTW, you'll have the support too; write the helptexts on how convenient this all is, and that it is really better for you, the user.

On a table with one million records, the view should retrieve the few child records you need quite quickly, if you use R.Optimization efficiently.

Then, indexing the view (as a result of the user double-clicking on the grid headers) is also quite fast, because the index is like a temp table, which only has a few records.


The framework I use is Visual Extend; it does the sorting automatically - I don't need to program anything for the sorting (including multi-column sort) to work. VFX (Visual Extend) uses existing indices if they exist, and creates missing indices as temporary (IDX) indices.

I don't actually have a million-record table. But with 60,000 records in a child table, editing became quite slow, until I used a view. Which is what Visual Extend recommends anyway.

Hilmar.