Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
>Hello, I have some questions about rushmore.
>
>Why does SYS 3054(11) say that the optimization level is partial or none when I am joining two tables on integer fields that have indexes? These are ordinary regular indexes.
>
>I'm joining 5 tables. Is VFP smart enough to use the where clause to filter out the majority of them before doing the join? One would think so. Ex:
>
>SELECT * FROM LN1, LN2 WHERE LN1.LNKEY = LN2.LNKEY ;
>AND LN1.NAME = 'SMITH'
>
>Does VFP find the SMITH's first, then join the result to LN2 using the key? Or does it in some circumstances join the tables first and then do the filter? I ask this because SYS 3054 says it does the join first.
That's why I believe "Rushmore" is "dumb" ... (and why UDF's can also play a role in some queries).
There's a very good possibility that the join occurs before the test for "SMITH" ... based on my experience in having to "wait" on these types of queries.
You might try changing the order of the "AND clauses": eg. "test", then "join" ... and see what happens.
However, even in this simple case, unless I'm sure I'll never have a large number of records, I will simply (1) Select "SMITH" into a cursor, and then (2) perform the join.
In coming up with an "access strategy", the thinking person will outperform Rushmore in all but the most trivial cases (IMO).
Précédent
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement