Newsgroup: it.comp.appl.access
Titolo: velocizzare una query composta prevalentemente da SUM

come da oggetto, partendo da una semplice tabella con tre campi
(MATCHCODE, Date, QtyMov) devo generare una cross-tab che mi raggruppa
per codice prodotto MATCHCODE e mi somma, prendendo come base la data
memorizzata in Parameters, i movimenti positivi e negativi per mese,
differenziandoli in carichi/scarichi e limitandosi a un anno
all'indietro nel tempo. 
 
I campi risultanti sono:
MATCHCODE
[Mov-11-In]
[Mov-11-Out]  
[Mov-10-In]
[Mov-10-Out]  
[Mov-9-In]
[Mov-9-Out]  
...
[Mov-2-In]
[Mov-2-Out]  
[Mov-1-In]
[Mov-1-Out]  
[Mov-0-In]
[Mov-0-Out]  
ai quali aggiungo anche i totali per l'anno
[Mov-Tot-In]
[Mov-Tot-Out]
 
 
la query, che funziona egregiamente ma impiega "troppo" tempo, 12"-15", 
copmincia
cosě: 
 
SELECT StockMovements.MATCHCODE, 
 
Sum(IIf((Year(StockMovements!Date)=Year(Forms!Parameters!EndDate) And
Month(StockMovements!Date)=Month(Forms!Parameters!EndDate)-11) Or
(Year(StockMovements!Date)=Year(Forms!Parameters!EndDate)-1 And
Month(StockMovements!Date)=Month(Forms!Parameters!EndDate)+1),IIf(StockMo
vements!QtyMov>0,StockMovements!QtyMov,0),0)) AS [Mov-11-In], 
 
Sum(IIf((Year(StockMovements!Date)=Year(Forms!Parameters!EndDate) And
Month(StockMovements!Date)=Month(Forms!Parameters!EndDate)-11) Or
(Year(StockMovements!Date)=Year(Forms!Parameters!EndDate)-1 And
Month(StockMovements!Date)=Month(Forms!Parameters!EndDate)+1),IIf(StockMo
vements!QtyMov<0,StockMovements!QtyMov,0),0)) AS [Mov-11-Out], 
 
Sum(IIf((Year(StockMovements!Date)=Year(Forms!Parameters!EndDate) And
Month(StockMovements!Date)=Month(Forms!Parameters!EndDate)-10) Or
(Year(StockMovements!Date)=Year(Forms!Parameters!EndDate)-1 And
Month(StockMovements!Date)=Month(Forms!Parameters!EndDate)+2),IIf(StockMo
vements!QtyMov>0,StockMovements!QtyMov,0),0)) AS [Mov-10-In], 
 
Sum(IIf((Year(StockMovements!Date)=Year(Forms!Parameters!EndDate) And
Month(StockMovements!Date)=Month(Forms!Parameters!EndDate)-10) Or
(Year(StockMovements!Date)=Year(Forms!Parameters!EndDate)-1 And
Month(StockMovements!Date)=Month(Forms!Parameters!EndDate)+2),IIf(StockMo
vements!QtyMov<0,StockMovements!QtyMov,0),0)) AS [Mov-10-Out], 
 
...
 
FROM StockMovements
GROUP BY StockMovements.MATCHCODE
ORDER BY StockMovements.MATCHCODE;
 
 
Ora, 50mila record (oggi) non sono tantissimi, mi aspetterei un tempo
decisamente inferiore, un paio di secondi, evidentemente la query non č 
ottimizzata 
 
Tra le idee malsane che mi son passate per la testa c'č quella di
pre-trattare la tabella con una intermedia, dove mi limito a calcolare
il delta-mese rispetto alla data di confronto (senza fare somme),
scartando automaticamente i record out-of-range e poi procedere con una
pivot (alla fine dell'elaborazione ottengo in media poco piů di 1400
righe)  
 
Avete altre idee?
 
-- 
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

Dovresti analizzare la Query con lo ShowPlan per capire come viene eseguita... e poi forse qualche spunto in più potrebbe venire.
A pelle la prima cosa che farei è di renderla "Paramatrica" per non dover inserire i riferimenti ai Controlli in Maschera, attenzione che essendo Raggruppata devi proprio definire in SQL i Parameters.
Poi proverei, solo per capire la differenza, a SEPARARE la query in 2, quella di Calcolo SUM e quella di Raggruppamento con OrderBy perchè temo possano essere eseguiti i calcoli più volte...
 
@Alex

 @Alex
 
proverň anche a separare le due fasi, grazie
 
nel frattempo ho tradotto in codice l'idea malsana, la pre-elaborazione 
impiega 3-4 secondi
 
----------------------------
 
Private Sub PrepareStockMovements()
 
' clean temp table CleanedStockMovements
' trash records out of range
' read record from StockMovements
' calc month displacement
' write temp
 
On Error GoTo Err_PrepareStockMovements
 
' set connections
Dim dbs  As Database
Dim Mov, Dest As DAO.Recordset
Set dbs = CurrentDb
 
Set Mov = dbs.OpenRecordset("Select * from StockMovements where DateAdd(" + 
Chr(34) + "yyyy" + Chr(34) + ",1,Date) > #" & Forms!Parameters!EndDate & 
"#", dbOpenDynaset)
Set Dest = dbs.OpenRecordset("CleanedStockMovements", dbOpenDynaset)
 
Dim InOut As String
 
Debug.Print Time
 
' clean Temp table
DoCmd.SetWarnings (False)
DoCmd.RunSQL ("Delete * from CleanedStockMovements")
DoCmd.SetWarnings (True)
 
' read StockMovements
Mov.MoveFirst
While Not Mov.EOF
    Dest.AddNew
    Dest![MATCHCODE] = Mov!MATCHCODE
    Dest![Manufacturer] = Mov!Manufacturer
    Dest![Name] = Mov!Name
    Dest![Date] = Mov!Date
    Dest![Description] = Mov!Description
    Dest![QtyMov] = Mov!QtyMov
    If Mov!QtyMov > 0 Then InOut = "-In" Else InOut = "-Out"
    If Year(Mov!Date) = Year(Forms!Parameters!EndDate) Then
        Dest![MonthDisplacement] = "Mov" & (Month(Mov!Date) - 
Month(Forms!Parameters!EndDate)) & InOut
    Else
        Dest![MonthDisplacement] = "Mov" & (Month(Mov!Date) - 
Month(Forms!Parameters!EndDate) - 12) & InOut
    End If
 
    Dest.Update
    Mov.MoveNext
Wend
 
Mov.Close
Dest.Close
Debug.Print Time
 
Stop
 
 
Exit_PrepareStockMovements:
    Exit Sub
 
Err_PrepareStockMovements:
    MsgBox Err.Description
    Resume Exit_PrepareStockMovements
 
End Sub
 
----------------------------
 
domani ragiono sulla PIVOT per vedere cosa si risparmia


 
 
 
SE questi movimenti NON variano nel tempo potresti calcolarne il totale 
una sola volta e salvarti il risultato della sommatoria in una tabella 
"TotaliAnniMesi"
anno/mese/codice/tot+/tot-
 
quando tra un mese rifarai la stessa statistica, 11 mesi li hai gia 
pronti nella tabella "totaliAnniMesi" e ti basta calcolare solo quelli 
del 12esimo, quindi un limitato numero di record da analizzare.
 
 
BFS
 
 

 
 
esatto anche questo. i dati arrivano una volta al mese, i primi giorni, 
relativi a quello appena chiuso e - di solito - non vengono più modificati 
(è successo una sola volta in tre anni abbondanti)
 
-- 
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [  da casa  ] ...........