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).
I'm finding that this view is slow when I try this from my laptop connected to the server (laptop: Pentium 133 32MB RAM win95 - server: PIII 600 128MB RAM win98). It takes about 20 seconds to gather about 30 records from the above view.
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)
Shouldn't rushmore be using the iid tags instead of ddd? Is there any way to force it to use a particular tag? 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?
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
Low-carb diet not working? Try the Low-food diet instead!