Mike Yearwood
Toronto, Ontario, Canada
Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
>>Hi Dodi
>>
>>>SELECT * FROM tacust WHERE tacust.cmrno = ?mcmrno AND tacust.actflag = "1"
>>>(in the form, the user enters a customer number (into the variable mcmrno) then it requeries the view)
>>
>>You can get some minor improvement by adding m. like this...
>>
>>where tacust.cmrno = ?m.mcmrno
>>
>>>
>>>CMRNO is already indexed in TACUST file.
>>>
>>>I did some research and tried different approaches. Tried using DEL tag on DELETED() but no improvement. I also tried adding ACTFLAG as index (for Rushmore optimization) but it did not help. SYS(3054,1) stated that Rushmore optimization level for table TACUST is none. I don't understand.
>>
>>The fact that it's saying no optimization means there's something wrong with your indexes and/or where clauses. For example if you have an index on UPPER(cmrno) then you query with WHERE cmrno = ?m.mcmrno, that cannot optimize. You'd have to change the where clause to WHERE UPPER(cmrno) = ?m.mcmrno
>
>Hello Mike, this is why I don't understand - the index and the where clause are exactly the same, CMRNO + ACTFLAG. :-\
Could you clarify that? CMRNO + ACTFLAG is not the same as WHERE UPPER(cmrno) = something AND actflag = somethingelse.
To use an INDEX ON CMRNO + ACTFLAG, your where clause would have to be WHERE CMRNO+ACTFLAG = ?m.mcmrno+"1"
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