Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
LEFT JOIN and complex criteria
Message
De
25/09/2006 13:32:26
 
 
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 9 SP1
OS:
Windows XP
Database:
Visual FoxPro
Divers
Thread ID:
01156530
Message ID:
01157000
Vues:
12
This message has been marked as a message which has helped to the initial question of the thread.
>>>Fabio,
>>>
>>>This select is quite slow. Do you know if it is possible to make it quicker?
>>>
>>>select curTemp.cTrans_pk, TI.cInsurances_fk ;	
>>>		from curTemp inner join Trans_Insurances TI ;
>>>		on curTemp.cTrans_pk = TI.cTrans_fk ;	
>>>		where TI.iSequence_Number = (select max(iSequence_Number) ;
>>>		from Trans_Insurances X ;
>>>		where X.cTrans_fk = TI.cTrans_fk) ;
>>>		into cursor curTemp1
>>
>>with VFP it is hard
>>
>>before posting 10 solutions,
>>you could furnish me these information:
>>
>>- reccounT(curTemp)/reccount(Trans_Insurances) ratio
>
>curTemp may have from 0 to 20 (or more records). Trans_Insurances is currently 400K+ records.
>
>>- is curTemp.cTrans_pk a pk
>
>Yes, cTrans_pk is a primary key
>
>>- is TI.cTrans_fk a pk or for every curTemp.cTrans_pk the join has many TI.cTrans_fk
>
>I may not have trans insurances for particular trans or I may have several (no more than 3 usually, in most cases just one).
>
>I decided to break my select hoping to get a speed gain (originally I did it in one select using LEFT JOIN and IsNull condition in where). I got a slight speed improvement...

I have trust in this
SELECT TI.cTrans_fk, TI.cInsurances_fk;
    FROM (select curTemp.cTrans_pk , max(T.iSequence_Number) iSequence_Number;
	    	from curTemp JOIN Trans_Insurances T ON curTemp.cTrans_pk = T.cTrans_fk ;
		    GROUP BY 1) X ;
		    JOIN Trans_Insurances TI ;
                 ON TI.cTrans_fk = X.cTrans_pk AND TI.iSequence_Number = X.iSequence_Number;
 into cursor curTemp1
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform