>Hi Folks -
>We have the following statement in our code which peforms OK on a table size of less than 10,000 records, anything greater we are running into performance issues. Can anyone advise how we could correct it:
>
>...
>select prod_tran
>set filter to
>set order to
>set exact on
>SELECT * FROM PRO_SCHT INTO CURSOR PROSCHT WHERE ALLTRIM(PRO_SCHT.SPFLAG)="*"
>SELECT 0000000000.000 AS CNTRANGE ,* FROM proscht a INTO DBF &pscht where EXISTS(SELECT * FROM PROD_TRAN b WHERE (a.p_number=b.p_number AND ALLTRIM(b.TRANNUMBER)=ALLTRIM(m.trannumber)))
>
>PSCHTA=ALIAS()
>....
I suggest you read my FAQ on Rushmore optimization for a start.
Do you have an index on DELETED()? Get rid of it.
Adding an index on p_number, on the other hand, might help, too.
The filter, or lack thereof, isn't required for SELECT - SQL, since this commands internally uses the equivalent of USE...AGAIN.
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)
