Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Hiya John.
Indices are cool.
How many records we talking about here for the base tables?
More comments below.
mondo regards [Bill]
>I have such statements when adding production items to my table (ROLLS). These statements undoubtedly slow down the operation which varies from computer to computer, depending on it's speed, but is even relatively slow with a Pentium IV.
>
>Is there anything I can do to really speed up the execution of these SQL statements and also not jeopordize theintegrity of the results?
>
>SELECT * FROM ROLLS WHERE UPPER(ALLTRIM(cSTATUS))=="REJECTED" AND ROLLS.cWorkOrderID == SALEORDER_ITEMS.cWorkOrderNo ORDER BY nRoll INTO CURSOR tmpRejected
>
index1 -> UPPER(ALLTRIM(ROLLS.cSTATUS)
index2 -> ROLLS.cWorkOrderID
index3 -> SALEORDER_ITEMS.cWorkOrderNo
index4 => ______.nRoll
>SELECT * FROM ROLLS WHERE UPPER(ALLTRIM(cSTATUS))=="ACCEPTED" AND ROLLS.cWorkOrderID == SALEORDER_ITEMS.cWorkOrderNo ORDER BY nRoll INTO CURSOR tmpAccepted
>
[same as first 4]
>SELECT nWeight_Roll, ;
>SUM(nWeight_Roll) AS nWeight_Skid, ;
>SUM(nWeight_Roll_Metric) AS nWeight_Skid_Metric, ;
>SUM(nUPerRoll) AS nUnits_Skid, ;
>SUM(nFootage_Roll) AS nFootage_Skid, ;
>cSkidID ;
>FROM ROLLS ;
>WHERE ROLLS.cWorkOrderID = SALEORDER_ITEMS.cWorkOrderNo ;
>GROUP BY 6 ;
>ORDER BY 6 ;
>INTO CURSOR tmpSkid
[same as first 4 plus]
index 5 -> ROLLS.cSkidID
That should speed things up at a minimum, by a factor of 50 percent.
But - the UPPER(ALLTRIM(___ thing sounds like you got some design changes for acceptable data usage for Accepted/Rejected, right ? why not somewhere give a ONE CHARACTER COLUMN for the ROLLS table that holds A or R [plus what ever else is there] ? Basically - you are doing a string function inside a sql statement, and that eats up processing time, no matter if it has the same index or not.
SO IF YOU HAVE THE ROOM TO ADD ONE MORE COLUMN - do so, and issue three ONE LINE COMMANDS, perhaps prior to your sql select statment ??? [not sure how you want to couple the additional column usage, but this is a one-off, so you can add it in to your design]
select rolls
replace onecolstat with "R" for UPPER(ALLTRIM(cSTATUS))=="REJECTED" and empty(onecolumnStat)
replace onecolstat with "A" for UPPER(ALLTRIM(cSTATUS))=="ACCEPTED" and empty(onecolumnStat)
then going forth ? Take Tore's comment about setting a field value to a variable , for example, ->
lcWorkOrderNo = SALEORDER_ITEMS.cWorkOrderno
SELECT * FROM ROLLS WHERE onecalstat =="R" AND ROLLS.cWorkOrderID == lcWorkOrderNo ORDER BY nRoll INTO CURSOR tmpRejected
Précédent
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