General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
SQL Won't Optimize when join in use...
Hey all,
Take the following SQL statement:
SELECT hpaph0.* FROM hpaph0 ;
WHERE hpaph0.aphst_ = 34 ;
AND hpaph0.aphcty = 77 ;
AND hpaph0.aphpol = 60051 ;
AND hpaph0.aphyr = 1 ;
INTO CURSOR cResult
Using SYS(3054,11) VFP (version 6, SP3) tells me that table hpaph0 is FULLY optimized. This is to be expected since index tags exist for aphst_, aphcty, aphpol, and aphyr.
Now, take the following:
SELECT hpaph0.* FROM hpaph0, arappx ;
WHERE hpaph0.aphst_ = arappx.appst ;
AND hpaph0.aphcty = arappx.appcty ;
AND hpaph0.aphpol = arappx.apppol ;
AND hpaph0.aphyr = arappx.appcyr ;
INTO CURSOR cResult
The only difference here is that I have added a table (to do a join) called arappx. Instead of using literal values I am using fields from arappx in order to do a join. For this, VFP tells me that table hpaph0's optimization is NONE. What gives? I was under the impression that as long as Fox could match what was on the left side of the comparisons to index tags, some Rushmore would kick in, at least for one of the tables. Am I high? The table arappx in this example has no indexes whatsoever, but shouldn't Rushmore still be able to optimize because of the hpaph0 tags? The arappx table is only around 5-10 records.
If I am just way off, how does one do an optimized join such as this? I tried using INNER JOIN syntax, thusly:
SELECT hpaph0.* FROM hpaph0 INNER JOIN arappx ;
ON hpaph0.aphst_ = arappx.appst ;
AND hpaph0.aphcty = arappx.appcty ;
AND hpaph0.aphpol = arappx.apppol ;
AND hpaph0.aphyr = arappx.appcyr ;
INTO CURSOR cResult
And got the same response -- zero optimization. Could somebody enlighten me as to where I am going wrong here?
Thanks muchly.
JoeK
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only