Bill,

This issue is important for us too. BTW, take a look into thread
Databases,Tables, Views, Indexing and SQL syntax Re: SQL suggestions needed Thread #331812 Message #332390. Right now I'm discussing the same problems. I'm very concerned about speed issue. I found that the right outer joins are very time consuming, at least in my test.
Anyway, we can not use LEFT or RIGHT OUTER joins in old 89 syntax.

>
>I've worked with the Select - SQL syntax ever since the 2.0 days and have been able to make it sing. I've become quite proficient in writing SQL-89 syntax compliant views/statements using the WHERE AND AND ...
>
>For some time now, we've have the SQL 92 compliant INNER JOIN/OUTER JOIN syntax. I've played with it a bit and am familiar with how it works.
>
>I tried comparing apples and apples (admittedly, this was over a year or so ago) and the time for WHERE vs. JOIN syntax wasn't close -- a second or so versus minutes for the JOIN syntax. My take on it at the time was the the JOIN syntax was joining the tables and then applying the filter (the WHERE clause) while the 89 syntax was applying the filter, then the join. I didn't prove it though, it just felt right and so I kept on coding in SQL 89 syntax.
>
>MSFT and others have told me that on occasion, the JOIN syntax is faster than the WHERE clause but I haven't found a documented case as of yet. I know MSFT would like us to shy away from SQL 89 syntax and have hinted that this feature will now be unsupported (perhaps a better way to phrase it is that no further optimization will be done on the SQL 89 type syntax)
>
>Assume for the moment that Rushmore has kicked in and I have indexes and primary keys where they should be. Given 2 somewhat large tables and a large table in between acting as a cross reference table for a many to many relationship, the WHERE clause statement to pull out records in the second somewhat large table based on a key field in the first would look like the following:
>
>SELECT SomewhatLargeTable2.cAttribute, SomewhatLargeTable2.iID FROM LargexRef, SomewhatLargeTable2 WHERE LargexRef.iTable1ID = ?vp_iID AND LargexRef.iTable2ID = SomewhatLargeTable2.iID
>
>vp_iID is the parameter, LargexRef.iTable1ID is the pointer to the first somewhat large first table and LargexRef.iTable2ID is the pointer to the somewhat large second table (Somewhat large being defined as about 200,000 records). This query will pull anywhere from 0 to say 100 or so records.
>
>What is the SQL 92 syntax that will give comparable (if not better) performance in VFP? What are the rules/hueristics by which the SQL 92 syntax runs faster than the SQL 89 syntax?

If it's not broken, fix it until it is.


My Blog