>I am a little confused over something regarding Rushmore.
>
>The following two tables don't seem to optimize when I check with SYS(3054,11).
>
>The two tables are not part of a database and belong to an very basic order entry system; tables are called order and orderitems.
>
>Order has a character field called cOrderNum that is unique and does have an index on this field.
>
>OrderItems does not have a unique field name, but does have a field also called cOrderNum that relates back to the Order table and also has an index.
>
>When I do a:
>SELECT * FROM Order, OrderItems WHERE Order.cOrderNum = OrderItems.cOrderNum
>
>It takes a long time (not many records 10,000 orders; 20,000 OrderItems) 3 seconds.
Times of a few seconds are to be expected if you select thousands of records.
>SYS(3054) says that the query is not optimized and I don't see why!
Probably the query is missing an index on deleted(). If you have SET DELETED ON, the condition "... and not deleted()" is added implicitly.
Adding the index on both tables would make your query full R.O., according to sys(3054). However, it will also make the query slower, at least in many cases. For an explanation why this is so, you may want to read my article about Rushmore, at
http://www.levelextreme.com/wconnect/wc.dll?FournierTransformation~2,84,14,8109.
Hilmar.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)