>Hi,
>
>I have a view based on the following sql statement:
>
>

>SELECT requests.*, providers.lname, providers.fname, ;
>updatestatus.cstatus, ;
>alltrim( pr_address.address1) + " " + alltrim( pr_address.address2) ;
>as address ;
>FROM rac!requests INNER JOIN rac!providers ;
> on providers.iid = requests.iproviderid ;
>INNER JOIN  pr_address ;
> on pr_address.prov_id = providers.iid ;
>INNER JOIN rac!updatestatus ;
> on updatestatus.iid = requests.iupdatestatus ;
>WHERE Requests.icasedefid = ?vp_casedefid ;
>ORDER BY Requests.dracdate, Requests.irac_no
>

>
>requests.dbf has about 150,000 records
>providers.dbf has about 30,000 records
>updatestatus.dbf has about 700 records
>
>all the tables have the right index tags:
>requests has a tag on iid called iid (primary key),
>providers has a tag on iid called iid (primary key),
>updatestatus has a tag on iid called iid (primary key).

Add the following tags:
Requests on icasedefid
PR_Address on Prov_Id

>
>I have tried adding index tags on deleted() (I name these tags 'ddd') and using set delete on and off and find no differences.
>
>Eview's optimization reports the following:
>Rushmore optimization level for table requests: partial
>Using index tag Ddd to rushmore optimize table providers
>Rushmore optimization level for table providers: full
>Rushmore optimization level for table pr_address: none
>Using index tag Ddd to rushmore optimize table updatestatus
>Rushmore optimization level for table updatestatus: full
>Joining table updatestatus and table requests (Cartesian product)
>Joining intermediate result and table providers (Cartesian product)
>Joining intermediate result and table pr_address (Cartesian product)

You'll never get full optimization on the query. See the Rushmore KB article on my website.

>
>Shouldn't rushmore be using the iid tags instead of ddd?

Rushmore will use what ever tag exactly matches the statement on the left-hand side of the =.

>Is there any way to force it to use a particular tag?

No

>Is there anything I can do with the sql statement in terms of the order in which I join the tables that could speed this up to a reasonable level?

Use the FORCE keyword in the SQL SELECT to join tables in the order listed.

>
>I think there's something wrong with this picture; 20 seconds to bring up 30 records from a relatively small table (as compared to some BEASTS I've heard about) doesn't seem right to me.
>
>Thanks!!!!
>
>Alex