SQL speed

[MrDeej]

Hello guys.

I have a SQL table which uses ODBC from Access. I have a relatively large table with 850 000 rows. Today i tried to update a field in the table from NULL to a textvalue.

I clocked the speed of the update to 3300 rows a minute, what do u think about the speed?

Dim t As Long
Dim d As Date
Dim dt As Date
Dim i As Long
Dim prmin As Long
d = Now
t = 1
Dim rst As New ADODB.Recordset
    With rst
    .Open "SELECT * from [LOK Loksummer - flytthistorikk] where hendelse = 'batchpakking' and pakkealternativ is null", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        Do Until .EOF
        .MoveNext
        i = i + 1
        Loop
        .MoveFirst
        Do Until .EOF
        t = t + 1
        !pakkealternativ = "1/1"
        .Update
        If Right(t, 1) = 0 Then
            prmin = t / DateDiff("s", d, Now)
            Me.Etikett12.Caption = t & " av " & i & " er oppdatert" & vbCrLf & "Pr minutt:" & prmin * 60 & vbCrLf & "Gjenstående:" & i - t & vbCrLf & "Ferdig klokka:" & DateAdd("s", (i - t) / prmin, Now)
            Me.Repaint
        End If
        .MoveNext
        Loop
    .Close
    End With

What do u think about the speed? Anybody got any tips to make this operation faster?

[MrDeej]

One more test
Changed from AdLockOptimisti c to AdLockBatchOpti mistic

New speed: 4800 rows a minute

[ChipR]

How To Speed Up Data Access by Using BeginTrans & CommitTrans

[MrDeej]

New Code:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim wksp As DAO.Workspace
Dim sqltext As String
Dim i As Long
Dim t As Long
Dim d As Date
 
set wksp = DBEngine.workspaces(0)
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [LOK Loksummer - flytthistorikk].Hendelse, [LOK Loksummer - flytthistorikk].Pakkealternativ FROM [LOK Loksummer - flytthistorikk] WHERE ((([LOK Loksummer - flytthistorikk].Hendelse)='batchpakking') AND (([LOK Loksummer - flytthistorikk].Pakkealternativ) Is Null));", dbOpenDynaset)
 
wksp.begintrans
 
rst.MoveFirst
Do Until rst.EOF
i = i + 1
rst.MoveNext
Loop
rst.MoveFirst
d = Now
Do Until rst.EOF
    t = t + 1
    rst.Edit
    rst!pakkealternativ = "1/1"
    rst.Update
    If Right(t, 2) = 0 Then
            prmin = t / DateDiff("s", d, Now)
            Me.Etikett12.Caption = t & " av " & i & " er oppdatert" & vbCrLf & "Pr minutt:" & prmin * 60 & vbCrLf & "Gjenstående:" & i - t & vbCrLf & "Ferdig klokka:" & DateAdd("s", (i - t) / prmin, Now)
            Me.Repaint
        End If
rst.MoveNext
Loop
wksp.CommitTrans

new speed: 12000 rows a minute

What just happend to the speed? What is Access doing now that it didnt do before?

[ChipR]

I'm wondering how much the timing part of your code is actually slowing it down. Have you tried:

Dim startTime as Double
Dim finishTime as Double
startTime = Timer
'code to time
finishTime = Timer
MsgBox Format(finishTime - startTime, "0.000")

[MrDeej]

I'm wondering how much the timing part of your code is actually slowing it down. Have you tried:

Dim startTime as Double
Dim finishTime as Double
startTime = Timer
'code to time
finishTime = Timer
MsgBox Format(finishTime - startTime, "0.000")

No i havent tried because i still aint done with updating the 850 000 rows. So i am checking the speed real-time :=)

On the other hand i tried to set the update timing part to each 10 or 100 rows and the speed is unaffected by this.

But what have begintrans and comittrans done to my updating? Does it have effect on the SQL server? Does it have affect on other users sharing the table? I didnt understand much of Microsoft langue in the link :)

Thank you anyway for quadrupling my speed :=):=)

[ChipR]

Instead of making each change as you .Update, they are all saved in a transaction and written at once, if I understand it correctly.

But why don't you use

UPDATE [LOK Loksummer - flytthistorikk] _
SET pakkealternativ = '1/1' _
WHERE [LOK Loksummer - flytthistorikk].Hendelse = 'batchpakking' _
AND ([LOK Loksummer - flytthistorikk].Pakkealternativ Is Null)

[MrDeej]

Instead of making each change as you .Update, they are all saved in a transaction and written at once, if I understand it correctly.

But why don't you use

UPDATE [LOK Loksummer - flytthistorikk] _
SET pakkealternativ = '1/1' _
WHERE [LOK Loksummer - flytthistorikk].Hendelse = 'batchpakking' _
AND ([LOK Loksummer - flytthistorikk].Pakkealternativ) Is Null)

Acutally i have no other reason than with my code further up i can se and update the 'timing' part of the code pr row, whilst this code makes the whole operation without letting me see it.

As this operation takes 70 minutes i need something to stare at ;)


CPU usage = 2-3%
Memory usage = stable at 51164kb (msaccess.exe)

[ChipR]

I see what you mean :)
I wonder if you could try the UPDATE statement on a subset of the records. Is there a field you can use in the WHERE clause to specify just several thousand rows and time that?
I suspect that the UPDATE would be much quicker, but who wants to wait half an hour to find out, right?

[MrDeej]

I see what you mean :)
I wonder if you could try the UPDATE statement on a subset of the records. Is there a field you can use in the WHERE clause to specify just several thousand rows and time that?
I suspect that the UPDATE would be much quicker, but who wants to wait half an hour to find out, right?

I'll give it a try when finished. Only 13 000 rows left (of 850 000)

[MrDeej]

Last test

Rows: 30 000
Time taken: 186second
Rows pr minute: 9677

Code:

Dim wksp As DAO.Workspace
Dim starttime As Date
Dim enddtime As Date

startime = Now
MsgBox startime
Set wksp = DBEngine.Workspaces(0)
Set db = CurrentDb
wksp.BeginTrans
DoCmd.RunSQL ("UPDATE [LOK Loksummer - flytthistorikk] SET [LOK Loksummer - flytthistorikk].Pakkealternativ = '1/1' WHERE ((([LOK Loksummer - flytthistorikk].autonr)<367916) AND (([LOK Loksummer - flytthistorikk].Hendelse)='batchpakking'));")
wksp.CommitTrans
endtime = Now

MsgBox DateDiff("s", startime, endtime)

Now im going home to eat dinner. Time in Norway is now 19:23
Byebye

[ChipR]

Thanks for the results. Good stuff!

[NeoPa]

What do u think about the speed? Anybody got any tips to make this operation faster?

I know you're probably all done with worrying about this now, with some good tips from Chip, but can you tell me whether or not any of the updates you're applying overlap with any index data?

If so, a tip would be to remove any indices that would be affected prior to running the job, then add them again afterwards. Updating key fields is a whole heap of pain above and beyond the simple updating of the data.

Obviously the RDBMS will handle the complexities for you, but the extra work done for each record, multiplied by the large number of individual records, can certainly clock up the microseconds ;)

[MrDeej]

Hello! The updatet field is non-indexed. Just a default text field :=)
But thank you for the tips

[NeoPa]

No worries.

Besides, I was also keen to have it mentioned for the benefit of anyone else who found the thread while researching their own problems.

In your case it is a non-issue of course :)