Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Sub-select speed issue
Message
De
21/11/2007 05:26:19
 
 
À
20/11/2007 22:21:50
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 SP2
OS:
Windows XP SP2
Database:
Visual FoxPro
Divers
Thread ID:
01270387
Message ID:
01270421
Vues:
24
Since the result cursor is quite small, I'ld create that first, with bKbKey added as column.
Then get a cursor of distinct bKbKey values
then calculate the aTotal field for that cursor and make a final select combining both cursors from there.

Might make a difference in cache behaviour....
select A.Field1, B.Field2, C.Field3, D.Field4, B.kBKey ;
  from      AFile A  ;
       join BFile B on B.kBKey1 = A.kAKey ;
  left join CFile C on B.kBkey2 = C.kCkey ;
  left join DFile D on B.kBKey3 = D.kDKey ;
 where A.AKey = ?gkKey       ;
 order by C.Field3, D.Field4 ;
 into cursor T_res

select kBKey from T_Res group by 1 into Cursor T_Key

Select E.kBKey, SUM(E.Field5) ;
   from EFile E ;
   Join T_Key on E.kEKey = T_Key.kBKey ;
   Group by 1 ;
   into cursor T_SUM

select Field1, Field2, Field3, Field4, aTotal ;
   from T_Res ;
   Join T_Sum ;
   on T_Res.kbKey == T_Sum.KbKey
perhaps create the T_RES already with the aTotal field and Readwrite for a later update.
At least you can get a feel which step costs time if you build distinct steps.

But those steps probably will always give you a perf hit.

my 0.02 EUR

thomas
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform