- Don't SET ORDER before running the SELECT. It isn't needed and may actually slow down the SELECT statement. The SELECT (alias) isn't needed either, but it won't affect the speed of the SELECT.
- In the first example, you need an index on gecode
- In the second example, you need an index on spnumber, but you'll never get full optimization
- If you have deleted records, you'll get different optimization results with SET DELETED ON/OFF and a tag on DELETED().

Look at the Rushmore article on my web site for more information.


>I am having trouble getting my head around SQL rushmore optimiztion. Using the sys(3054) function, I get excited if I can score Partial optimization. Often, none though. So, I guess my SQL and/or indexing is up the creek. In this example, the first SELECT gives partial optimization, the second none.
>
>* assemble a list of species codes from SP
>SELECT SP
>SET ORDER TO GECODE
>SELECT spnumber FROM species into CURSOR mycodes WHERE gecode = gecodex
>
>* use species codes to query the required records from HS
>SELECT HS
>SET ORDER TO SPNUMBER
>SELECT * FROM COLLECTIONS INTO TABLE (tempx) WHERE SPNUMBER IN (SELECT SPNUMBER FROM mycodes)
>
>As far as I can see, the indexes are OK. Is a better or 'correct' way to do this. THANKS.