473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL speed

157 100+
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
14 1867
MrDeej
157 100+
One more test
Changed from AdLockOptimistic to AdLockBatchOptimistic

New speed: 4800 rows a minute
Jan 26 '09 #2
MrDeej
157 100+
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
ChipR
1,287 Expert 1GB
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
MrDeej
157 100+
@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
ChipR
1,287 Expert 1GB
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
MrDeej
157 100+
@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
ChipR
1,287 Expert 1GB
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
MrDeej
157 100+
@ChipR
I'll give it a try when finished. Only 13 000 rows left (of 850 000)
Jan 26 '09 #10
MrDeej
157 100+
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
ChipR
1,287 Expert 1GB
Thanks for the results. Good stuff!
Jan 26 '09 #12
NeoPa
32,556 Expert Mod 16PB
@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
MrDeej
157 100+
Hello! The updatet field is non-indexed. Just a default text field :=)
But thank you for the tips
Jan 27 '09 #14
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

13
by: Yang Li Ke | last post by:
Hi guys, Is it possible to know the internet speed of the visitors with php? Thanx -- Yang
8
by: Rob Ristroph | last post by:
I have tried out PHP 5 for the first time (with assistance from this group -- thanks!). The people I was working with have a site that uses lots of php objects. They are having problems with...
34
by: Jacek Generowicz | last post by:
I have a program in which I make very good use of a memoizer: def memoize(callable): cache = {} def proxy(*args): try: return cache except KeyError: return cache.setdefault(args,...
28
by: Maboroshi | last post by:
Hi I am fairly new to programming but not as such that I am a total beginner From what I understand C and C++ are faster languages than Python. Is this because of Pythons ability to operate on...
52
by: Neuruss | last post by:
It seems there are quite a few projects aimed to improve Python's speed and, therefore, eliminate its main limitation for mainstream acceptance. I just wonder what do you all think? Will Python...
7
by: YAZ | last post by:
Hello, I have a dll which do some number crunching. Performances (execution speed) are very important in my application. I use VC6 to compile the DLL. A friend of mine told me that in Visual...
6
by: Ham | last post by:
Yeah, Gotto work with my VB.Net graphic application for days, do any possible type of code optimization, check for unhandled errors and finally come up with sth that can't process 2D graphics and...
6
by: Jassim Rahma | last post by:
I want to detect the internet speed using C# to show the user on what speed he's connecting to internet?
11
by: kyosohma | last post by:
Hi, We use a script here at work that runs whenever someone logs into their machine that logs various bits of information to a database. One of those bits is the CPU's model and speed. While...
4
by: nestle | last post by:
I have DSL with a download speed of 32MB/s and an upload speed of 8MB/s(according to my ISP), and I am using a router. My upload speed is always between 8MB/s and 9MB/s(which is above the max upload...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.