Hej!

Jag kör idag nedanstående vba-kod på en kolumn i en tabell som innehåller 36.000 rader på en 2ghz pentium4-maskin. Körtiden är 30 minuter.

Finns det någon sätt att snabba upp körtiden?
Idag görs select, beräkning och update, kan man göra en update utan select med case-sats i?

<code>
Private Sub Uppdatera_Click()
On Error GoTo Err_Uppdatera_Click

Dim msgRC As String
Dim dbs As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rst As Recordset
Dim wFöretag As String, wKat As Integer, wArtnr As String, wStl As Integer, wSida As Integer, wORD_UT_EST As Double
On Error GoTo errorhandler

msgRC = MsgBox("Vill du verkligen fortsätta? Alla Artiklar uppdateras!", vbOKCancel)

If msgRC = vbOK Then
Set dbs = CurrentDb

strSQL = "SELECT företag, kat, artnr, stl, sida, ORD_UT_EST FROM Artiklar WHERE NOT ISNULL(Benämning);"
Set qdf = dbs.CreateQueryDef("", strSQL)
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

Do Until rst.EOF
wFöretag = rst(0)
wKat = rst(1)
wArtnr = rst(2)
wStl = rst(3)
wSida = rst(4)
If IsNull(rst(5)) Then
wORD_UT_EST = 0
Else
wORD_UT_EST = rst(5)
End If

Dim Intal As Double
Dim lngTemp As Long
Dim lngOnes As Long

Intal = wORD_UT_EST

' Round to nearest Integer
lngTemp = Int(Intal + 0.5)

' Get "Ones" digit
lngOnes = lngTemp Mod 10
Select Case lngOnes
Case Is >= 3
lngTemp = lngTemp + 9 - lngOnes
Case Is <= 2
lngTemp = lngTemp - lngOnes - 1
End Select

' Om värdet in var 0 ges -1 ut, sätts om till 0
If lngTemp = -1 Then
lngTemp = 0
End If

strSQL = "UPDATE Artiklar SET ORD_UT_EST = " & lngTemp
strSQL = strSQL & " WHERE NOT IsNull(Benämning) AND Artnr = '" & wArtnr & "' "
strSQL = strSQL & " AND Stl = " & wStl & " AND Företag = '" & wFöretag & "' AND Kat = " & wKat
DoCmd.RunSQL (strSQL)

rst.MoveNext
Loop

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

MsgBox ("Uppdatering klar i artikeltabellen.")
Else
MsgBox ("Uppdatering avbruten")
End If

Exit Sub

errorhandler:
MsgBox "Artnr: '" & wArtnr & " '"
MsgBox "Företag = '" & wFöretag & "'"
MsgBox "OrdUtEst = '" & lngTemp & "'"
MsgBox "Kat = '" & wKat & "'"
MsgBox "Sida = '" & wSida & " '"
MsgBox "Sqlstr = '" & strSQL & "' "
MsgBox "Felmeddelande=" & Err.Number & " Beskrivning=" & Err.Description & " Modul=Uppdatera_Click()"

Exit_Uppdatera:
Exit Sub

Err_Uppdatera_Click:
MsgBox Err.Description
Resume Exit_Uppdatera

End Sub
</code>

Hej, och välkommen till pellesoft.

Eftersom du redan adderat informationen är det där du skall göra dina beräkningar och därmed slippa det extra arbete som behövs för att rätta till allting. Hade du haft sql-server hade vi lätt kunnat skriva en lagrad procedur med lite if-kommandon men eftersom du kör access får vi söka en annan väg av optimering.

Först lite allmänt om optimering.

1. Du deklarerar fälten inuti loopen, det blir overhead - lägg deklarationen före du går in i loopen, annars tjänar du inget på detta.

Företag = rst(0)
wKat = rst(1)
wArtnr = rst(2)
wStl = rst(3)
wSida = rst(4)

Do Until rst.EOF

2. Du uppdaterar en post och använder en where-sats med flera alternativ. Här borde du redan från början få ut ett unikt id att uppdatera - dvs ett orderid,

3. När man ser att du har företag i tabellen artiklar så låter det även som du inte normaliserat din databas. Ett företagsnamn borde ligga i tabellen företag, artikeln i tabellen artikel och (saldo, eller order borde ligga i order med företagsid och artikelid samt oderid och antal).

4. Vad är skillnaden? Jo, eftersom access skyfflar datat till och från internminnet så blir det mindre data ju mindre dina tabeller är. Numeriskt data läses fort och tar liten plats. Normaliseringen gör att du inte behöver handskas med några textfält för just detta ärende.

5. Mitt förslag är att ta bort all kod utom rst.movenext och se hur lång tid det tar. Går det väldigt fort så vet du att loopen är trög. Sen ändrar du enligt förslag 1 , tar bort update och kollar igen. Går det snabbt även då så vet du att det är update som du måste optimera.

6. Att använda en timer för att se skillnaderna är bra, då får man en förståelse om vad som egentligen tar tid.

w! = timer
(lite kod)
msgbox timer -w!

7. Sist men inte minst
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

Testa att öppna recordsetet med andra parametrar, denna kanske läser upp hela tabellen felaktigt och i stort sätt låser datat i minnet eller håller dubbla mängden data och det är onödigt.

Hoppas detta ger dig några ideer att arbeta vidare på - lycka till.

Det är mycket effektivare att göra allt med SQL. För att förenkla och öka läsbarheten kan man skapa några temporära kolumner (istället för variablerna i din kod) och gör uppdateringen i flera steg. Hoppas att jag har gjort rätt nu (det är nog inte så stor chans...).

UPDATE Artiklar SET ORD_UT_EST = IIF(INT(IIF(IsNull(ORD_UT_EST), 0, ORD_UT_EST) + 0.5) MOD 10 > 2, INT(IIF(IsNull(ORD_UT_EST), 0, ORD_UT_EST) + 0.5) + 9 - (INT(IIF(IsNull(ORD_UT_EST), 0, ORD_UT_EST) + 0.5) MOD 10), INT(IIF(IsNull(ORD_UT_EST), 0, ORD_UT_EST) + 0.5) + 1 - (INT(IIF(IsNull(ORD_UT_EST), 0, ORD_UT_EST) + 0.5) MOD 10)) WHERE NOT ISNULL(Benämning)

UPDATE Artiklar SET ORD_UT_EST = 0 WHERE ORD_UT_EST = -1 AND NOT ISNULL(Benämning)


Som du ser är detta väldigt oläsligt eftersom man inte kan använda variabler i Access-SQL. Jag skrev detta i några olika steg:

ORD_UT_EST: IIF(IsNull(ORD_UT_EST), 0, ORD_UT_EST)

lngTemp: INT(IIF(IsNull(ORD_UT_EST), 0, ORD_UT_EST) + 0.5)

lngTemp: IIF(INT(IIF(IsNull(ORD_UT_EST), 0, ORD_UT_EST) + 0.5) MOD 10 > 2, INT(IIF(IsNull(ORD_UT_EST), 0, ORD_UT_EST) + 0.5) + 9 - (INT(IIF(IsNull(ORD_UT_EST), 0, ORD_UT_EST) + 0.5) MOD 10), INT(IIF(IsNull(ORD_UT_EST), 0, ORD_UT_EST) + 0.5) + 1 - (INT(IIF(IsNull(ORD_UT_EST), 0, ORD_UT_EST) + 0.5) MOD 10))


/Johan

Johans SQL-lösning är såklart den optimala.... Dvs om den fungerar, vilket jag inte har för avsikt att kontrollera.. :o]
Men den är kanske lite knepig att begripa, och framför allt underhålla..

Den stora flaskhalsen i den ursprungliga koden är uppbygggnaden av variabeln strSQL - 36.000 ggr samt såklart själva UPDATE-instruktionen som körs lika många ggr.. (på sannolikt icke indexerade kolumner??). Inte konstigt att det tar tid..

Du ska uppdatera alla rader, och du ska löpa igenom alla rader?
Varför inte gör det samtidigt (i samma varv)?
Dvs för varje post: gör din beräkning, uppdatera den aktuella posten i ditt recordset, och fortsätt. Det bör bli rätt mycket snabbare.

Ola