By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,477 Members | 1,602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,477 IT Pros & Developers. It's quick & easy.

SQL speed

100+
P: 157
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?
Expand|Select|Wrap|Line Numbers
  1. Dim t As Long
  2. Dim d As Date
  3. Dim dt As Date
  4. Dim i As Long
  5. Dim prmin As Long
  6. d = Now
  7. t = 1
  8. Dim rst As New ADODB.Recordset
  9.     With rst
  10.     .Open "SELECT * from [LOK Loksummer - flytthistorikk] where hendelse = 'batchpakking' and pakkealternativ is null", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  11.         Do Until .EOF
  12.         .MoveNext
  13.         i = i + 1
  14.         Loop
  15.         .MoveFirst
  16.         Do Until .EOF
  17.         t = t + 1
  18.         !pakkealternativ = "1/1"
  19.         .Update
  20.         If Right(t, 1) = 0 Then
  21.             prmin = t / DateDiff("s", d, Now)
  22.             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)
  23.             Me.Repaint
  24.         End If
  25.         .MoveNext
  26.         Loop
  27.     .Close
  28.     End With
  29.  
  30.  
What do u think about the speed? Anybody got any tips to make this operation faster?
Jan 26 '09 #1
Share this Question
Share on Google+
14 Replies


100+
P: 157
One more test
Changed from AdLockOptimistic to AdLockBatchOptimistic

New speed: 4800 rows a minute
Jan 26 '09 #2

100+
P: 157
New Code:
Expand|Select|Wrap|Line Numbers
  1. Dim rst As DAO.Recordset
  2. Dim db As DAO.Database
  3. Dim wksp As DAO.Workspace
  4. Dim sqltext As String
  5. Dim i As Long
  6. Dim t As Long
  7. Dim d As Date
  8.  
  9. set wksp = DBEngine.workspaces(0)
  10. Set db = CurrentDb
  11. 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)
  12.  
  13. wksp.begintrans
  14.  
  15. rst.MoveFirst
  16. Do Until rst.EOF
  17. i = i + 1
  18. rst.MoveNext
  19. Loop
  20. rst.MoveFirst
  21. d = Now
  22. Do Until rst.EOF
  23.     t = t + 1
  24.     rst.Edit
  25.     rst!pakkealternativ = "1/1"
  26.     rst.Update
  27.     If Right(t, 2) = 0 Then
  28.             prmin = t / DateDiff("s", d, Now)
  29.             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)
  30.             Me.Repaint
  31.         End If
  32. rst.MoveNext
  33. Loop
  34. wksp.CommitTrans
  35.  
  36.  
new speed: 12000 rows a minute

What just happend to the speed? What is Access doing now that it didnt do before?
Jan 26 '09 #4

Expert 100+
P: 1,287
I'm wondering how much the timing part of your code is actually slowing it down. Have you tried:
Expand|Select|Wrap|Line Numbers
  1. Dim startTime as Double
  2. Dim finishTime as Double
  3. startTime = Timer
  4. 'code to time
  5. finishTime = Timer
  6. MsgBox Format(finishTime - startTime, "0.000")
  7.  
Jan 26 '09 #5

100+
P: 157
@ChipR

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 :=):=)
Jan 26 '09 #6

Expert 100+
P: 1,287
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
Expand|Select|Wrap|Line Numbers
  1. UPDATE [LOK Loksummer - flytthistorikk] _
  2. SET pakkealternativ = '1/1' _
  3. WHERE [LOK Loksummer - flytthistorikk].Hendelse = 'batchpakking' _
  4. AND ([LOK Loksummer - flytthistorikk].Pakkealternativ Is Null)
Jan 26 '09 #7

100+
P: 157
@ChipR
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)
Jan 26 '09 #8

Expert 100+
P: 1,287
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?
Jan 26 '09 #9

100+
P: 157
@ChipR
I'll give it a try when finished. Only 13 000 rows left (of 850 000)
Jan 26 '09 #10

100+
P: 157
Last test

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

Code:
Expand|Select|Wrap|Line Numbers
  1. Dim wksp As DAO.Workspace
  2. Dim starttime As Date
  3. Dim enddtime As Date
  4.  
  5. startime = Now
  6. MsgBox startime
  7. Set wksp = DBEngine.Workspaces(0)
  8. Set db = CurrentDb
  9. wksp.BeginTrans
  10. 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'));")
  11. wksp.CommitTrans
  12. endtime = Now
  13.  
  14. MsgBox DateDiff("s", startime, endtime)
  15.  
Now im going home to eat dinner. Time in Norway is now 19:23
Byebye
Jan 26 '09 #11

Expert 100+
P: 1,287
Thanks for the results. Good stuff!
Jan 26 '09 #12

NeoPa
Expert Mod 15k+
P: 31,186
@MrDeej
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 ;)
Jan 27 '09 #13

100+
P: 157
Hello! The updatet field is non-indexed. Just a default text field :=)
But thank you for the tips
Jan 27 '09 #14

NeoPa
Expert Mod 15k+
P: 31,186
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 :)
Jan 27 '09 #15

Post your reply

Sign in to post your reply or Sign up for a free account.