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?
14 1867
One more test
Changed from AdLockOptimistic to AdLockBatchOptimistic
New speed: 4800 rows a minute
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?
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")
-
@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 :=):=)
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)
@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)
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?
@ChipR
I'll give it a try when finished. Only 13 000 rows left (of 850 000)
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
Thanks for the results. Good stuff!
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 ;)
Hello! The updatet field is non-indexed. Just a default text field :=)
But thank you for the tips
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 :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Yang Li Ke |
last post by:
Hi guys,
Is it possible to know the internet speed of the visitors with php?
Thanx
--
Yang
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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?
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
|
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...
| |