Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Problem with LEFT JOIN
Message
De
21/01/2005 14:01:14
Walter Meester
HoogkarspelPays-Bas
 
 
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Versions des environnements
Visual FoxPro:
VFP 8 SP1
OS:
Windows XP SP2
Divers
Thread ID:
00978845
Message ID:
00979389
Vues:
19
This message has been marked as the solution to the initial question of the thread.
Hi nadya,

>I could not make it work. And I found that OR prevents using indexes. That's a little bit strange. Why AND allows to use indexes and OR doesn't?

With OR, both sides of the expression need to be rushmore optimizable in order for the whole expression to be optimizable. However in complex situation even where all arguments are optimizable and from a logical point of view VFP could optimize the expression, VFP often finds the expression too complex and reverts to a table scan.

One example I got recently was:
SELECT ..... ;
	FROM  t_relations  ;
		LEFT JOIN t_Treatmen ON rel_cycleno = trm_cycleno AND rel_cycleno <> "      " ), t_demo ;
	WHERE (rel_acu_id = ?Demo.acu_id AND  dem_acu_id = rel_related_to) OR  ;
		(T_relations.rel_related_to = ?Demo.acu_id AND  dem_acu_id = rel_acu_id )
All field used in the WHERE clause were optimizable, but it did not optimize the expression. I found myself rewriting the statement into
SELECT ....
	FROM  t_relations   
		INNER JOIN t_Demo ON (dem_acu_id = rel_related_to AND rel_acu_id = ?demo.Acu_id)  
		LEFT OUTER JOIN t_Treatmen  ON  Rel_cycleno = trm_cycleno AND rel_cycleno > SPACE(10)  ;
UNION SELECT .... ;
	FROM  t_relations   ;
		INNER JOIN t_Demo ON dem_acu_id = rel_acu_id AND rel_related_to = ?demo.Acu_id ;
		LEFT OUTER JOIN t_Treatmen  ON  Rel_cycleno = trm_cycleno AND rel_cycleno > SPACE(10)
The latter was both in VFP and SQL server way and way quicker than the first mainly because of optimizing reasons. BTW, the example above are also (updatable) views.

Walter,
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform