Adding a deleted() tag to both tables help ?

index on deleted() tag deleted

Cetin

>Sergey,
>
>Thank for your help.
>
>

> SET COLLATE TO "MACHINE"
>
> a = SECONDS()
>  *-- On veut la somme des catégories ( OABCD ) et hospitalisation par type institution ,
>  *-- sur une période donnée ( D_debut , d_fin ) avec une extrapolation possible de 8 jours
>  *-- sur les HospItalisations.
>  SET SAFETY OFF
>
>  SELECT Etats
>  INDEX ON ddebut TAG ddebut ADDITIVE
>  INDEX ON dfin 	TAG dfin ADDITIVE
>  INDEX ON ntype 	TAG ntype ADDITIVE
>  INDEX ON nPoscat TAG nPoscat ADDITIVE
>
>
>  SELECT DatesFiltred
>  INDEX ON ntype  TAG ntypeIndex ADDITIVE
>  INDEX ON ddate TAG ddate ADDITIVE
>  INDEX ON nPoscat TAG oscatIndex ADDITIVE
>
>
>  SYS(3054,11,"coptimation")
>
>  SELECT DatesFiltred.nPoscat, ;
>    Etats.ccat_physiq  ,;
>    Etats.ntype , ;
>    COUNT( DatesFiltred.ddate ) AS "Ncat" , ;
>    Etats.ltransfert  ;
>    FROM  DatesFiltred ;
>    INNER JOIN amline!Etats ON DatesFiltred.nPoscat = Etats.nPoscat AND DatesFiltred.ntype = Etats.ntype AND ;
>    DatesFiltred.ddate BETWEEN Etats.ddebut AND Etats.dfin  ;
>    WHERE DatesFiltred.ddate BETW D_debut AND d_fin  ;
>    ORDER BY  ltransfert , DatesFiltred.ntype , DatesFiltred.nPoscat ;
>    GROUP BY  ltransfert , DatesFiltred.ntype , DatesFiltred.nPoscat ;
>    INTO CURSOR Sum_Categ_per_periode && CURSOR tempstat2
>
>  SYS(3054,0)
>
>
>  CREATE CURSOR TT ( gmemo M )
>  INSERT INTO TT ( gmemo ) VALUE( coptimation )
>  CLEAR
>  ? coptimation
>
>
>  * WAIT
>  B = SECONDS()
>  WAIT WINDOW NOWAIT TRANSFORM( B - a , "9999.99999")
>
>

>
>
>Using index tag Ddate to rushmore optimize table datesfiltred
>Rushmore optimization level for table datesfiltred: partial
>Rushmore optimization level for table etats: none
>Joining table etats and table datesfiltred using temp index
>
>No improvement
>
>
>
>
>
>>Try to add indexes on the JOIN fields 'nposcat' and 'nType' in both tables. You can aslo play with JOIN itself

    FROM  DatesFiltred ;
>>    JOIN Etats ON DatesFiltred.nposcat = Etats.nposcat AND DatesFiltred.nType = Etats.nType AND ;
>>     DatesFiltred.ddate BETWEEN Etats.ddebut AND Etats.dfin  ;
>>* or
>>    JOIN Etats ON DatesFiltred.nposcat = Etats.nposcat AND DatesFiltred.nType = Etats.nType ;
>>    WHERE DatesFiltred.ddate BETWEEN Etats.ddebut AND Etats.dfin  ;
>>

>>>Sergey,
>>>
>>>I make 3 index but Vfp don't want optimze my requery.
>>>Why ?
>>>
>>>
>>> SYS(3054,11,"coptimation")
>>>
>>>

>>>  SELECT DatesFiltred.nposcat, ;
>>>    Etats.ccat_physiq  ,;
>>>    Etats.nType , ;
>>>    COUNT( DatesFiltred.ddate ) AS "Ncat" , ;
>>>    Etats.ltransfert  ;
>>>    FROM  DatesFiltred ,Etats;
>>>    WHER DatesFiltred.nposcat = Etats.nposcat AND DatesFiltred.nType = Etats.nType AND ;
>>>     DatesFiltred.ddate BETWEEN Etats.ddebut AND Etats.dfin  ;
>>>    ORDER BY  ltransfert , DatesFiltred.nType , DatesFiltred.nposcat ;
>>>    GROUP BY  ltransfert , DatesFiltred.nType , DatesFiltred.nposcat ;
>>>    INTO CURSOR Sum_Categ_per_periode && CURSOR tempstat2
>>>
>>>  SYS(3054,0)
>>>
>>>Rushmore optimization level for intermediate result: none
>>>Rushmore optimization level for table etats: none
>>>Joining table etats and intermediate result using temp index
>>>
>>>

>>
>>>>
>>>>I don't see anything interesting in that mesage besides incorrect statement that BETWEEN isn't Rushmore optimizable. In fact it is.