>Table A with indexes on tCreated, iStopCode, iMachineID. In a view left outer joined to TableB on iCode into the iID PK for the table. TableA 1 million records, TableB 60 records. Filtered on iMachineID=?piMachine and iCode > 0, Setting view order to tCreated DESCENDING and have TopN set to 20 records. The ?piMachineID should filter TableA to about 10K records. Basically I'm trying to get the last 20 records for iCode>0.
>
>The view is taking on order of 1 minute to requery() obviously it's not optimizing. Is the TopN or the descending order of the view killing it?
I would say that neither of them. Still, for both of them, the index must be created. Did you use the sys(3054) to see the optimization status? Is an outer join optimizable?
I assume you also have an index on iCode and deleted().
Vlad
Précédent
Suivant
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