Saving Array to Recordset/Table 1

[PieSchie]

Hello There,

for performance-enhancement i am unsucsessfully looking for a complement of rs.getRows() to save data from array to a recordset/table without looping through the array, like

for x LBound(myArray,2) to UBound(myArray,2)
rs.addNew
rs!value1 = myArray(0,x)
rs!value2 = myArray(1,x)
rs!value3 = myArray(2,x)
'...
rs.Update
next x

I think there has to be any possibility because "Strg-C" - "Strg-V" in data-view mode is faster than the loop.

Thanks for any Tips that can help.

 

[jrbarnett]

Store the value of LBound and UBound calls in a variable, calling them once before the loop and then use the variables.

That way, you're only calling each function once.

You can also use an SQL insert statement instead of a recordset which is faster:

CurrentDb.Execute "insert into table (f1, f2, f3) values ('" & myArray (i, 2) & ",'" & myArray (i, 3) & "','" & myArray (i, 4) & "'"

John

 

[PieSchie]

Thanks for replying but ...

... LBound and UBound as variables does no real speedup with my code.

The "insert-into" Idea is great - I thought - but this is about 20 times slower than the addnew-thing.

Is there no copy-paste-like function as if I would copy-paste some range from excel to a table in access? This looks pretty fast - much faster than the "same?" algorithm in VBA.

 

[JerryKlmns]

PieSchie

Since AddNew method of the recordset object is faster then its equivalant INSERT INTO sql statement (I wonder why?) the next thing to test is "UpdateBatch" of a Client side cursor location.

 

[jrbarnett]

Can you post your amended code (the whole sub or function)? Just wondering what's happening as this seems to contradict what I've seen and heard many times.

John

 

[TheAceMan1]

How are ya PieSchie . . .

I agree with [blue]JerryKlmns[/blue]. [blue]Recordset AddNew[/blue] faster than [blue]SQL Insert![/blue] I'd love to see this [blue]miracle code.[/blue] Could you please post the code? . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[PieSchie]

So here it is :-/

I tried it on various systems. Still the same.
AddNew is much faster.

Sub RSWriteSpeed()
  Dim timer1 As Double
  Dim timer2 As Double
  Dim matrix() As Long
  Dim lb%, ub%, x%
  
  timer1 = 0
  timer2 = 0
  
  'Clear both Tables
  CurrentDb.Execute ("Delete * From Table1;")
  CurrentDb.Execute ("Delete * From Table2;")
  
  'Generate a Matrix with 5 columns and here 4000 rows
  Call fillMatrix(matrix, 4000)
  
  
  'measure time to copy the matrix with "AddNew"-Method to Table1
  timer1 = timer1 - Timer()
    Set rs = CurrentDb.OpenRecordset("Table1")
    lb = LBound(matrix, 2)
    ub = UBound(matrix, 2)
    For x = lb To ub
      rs.AddNew
      rs!id = matrix(0, x)
      rs!value1 = matrix(1, x)
      rs!value2 = matrix(2, x)
      rs!value3 = matrix(3, x)
      rs!value4 = matrix(4, x)
      rs!value5 = matrix(5, x)
      rs.Update
    Next x
  timer1 = timer1 + Timer()
  
  
  'measure time to copy the matrix with "Insert"-Method to Table2
  timer2 = timer2 - Timer()
    lb = LBound(matrix, 2)
    ub = UBound(matrix, 2)
    For x = lb To ub
      sqlstring = "insert into Table2 (id, value1, value2, value3, value4, value5) values (" & matrix(0, x) & "," & matrix(1, x) & "," & matrix(2, x) & "," & matrix(3, x) & "," & matrix(4, x) & "," & matrix(5, x) & ");"
      CurrentDb.Execute (sqlstring)
    Next x
  timer2 = timer2 + Timer()
  
  MsgBox "Timer1 = " & timer1 & vbCrLf & "Timer2 = " & timer2 & vbCrLf & "Faktor = " & timer2 / timer1
End Sub

Sub fillMatrix(matrix() As Long, rows As Long)
  ReDim matrix(5, rows)
  Dim x As Integer, y As Integer
  
  For x = LBound(matrix, 1) To UBound(matrix, 1)
    For y = LBound(matrix, 2) To UBound(matrix, 2)
      matrix(x, y) = y + 10 * x
    Next y
  Next x
End Sub

 

[jrbarnett]

Can you just confirm that these tables are indeed local to the Access database that the code resides in and not linked either through ODBC to a back end engine (SQL Server, Oracle, MySQL etc) or linked to another Access database?

John

 

[PieSchie]

Yes, the Test-DB is one single Access-DB with two Tables and one VBA-Module. I Access the Tables over "DAO 3.6 object library".

 

[jrbarnett]

I'm just trying to replicate this, and have noticed your code can't compile properly - you don't have a "Dim RS As Recordset" line, which means you can't have "Option Explicit" set at the top of your code.

Can you fix these and re run?

John

 

[jrbarnett]

Update:

Here are my totally non scientific results

With table fields as text (50) fields
Run 1 - with code as above
Run 2 - adding Option explicit, and dim RS as Recordset and sqlstring as string

Run 3 - As 2 but converting the tables to long fields
Run 4 - using CurrentProject.connection.Execute instead of CurrentDb.Execute to run the SQL string (only expected in run 1)

Run          1      2      3      4
Recordset  32.18   31.84  32.0  31.48
SQL insert 80.31   79.78  79.37 61.09
Factor      2.49    2.50   2.53  1.94

Conclusions from this mini not particularly well controlled experiment were that PieSchie is correct. I could possibly improve it further by using an ADO recordset rather than a DAO recordset.

My equipment was: HP dc7600 PC, dual core 2.8GHz Pentium D, 2Gb RAM. Gigabit ethernet connection (database was on network drive). Anybody else care to elaborate on this?

John

 

[JerryKlmns]

Run 3 Recordset = 0,140625
Run 3 SQL insert= 22,0625
Run 4 SQL insert= 5,3671875

with Dim rs As DAO.Recordset, local mdb, local tables no other tables one module.

Using a With .... End With block for the rs Run 2 =0,140625
Also using ordinal position of fields Run2 = 0,12109375

Just out of curiosity for 1 record added, Run 2 = 0 others 0,0078125

Should we use something different for time elapsed?

Testing on
HP
CPU: P4 3.0GHz
RAM: 1.24GB
HDD: 80GB Partition 34.1GB Free Space 26.1GB
OS : WinXPsp2
Acess 2k3 format A2k

Reminds me a test on Fastest method to count the lines of a txt file.

 

[Rincewind123456]

Additional non scientific (almost disturbing) results:

Runs as declared by jrbarnett:
Run 1 2 3 4
Recordset 0,19 0,08 0,06 0,14
SQL insert 2,28 2,30 2,53 2,78
Factor 12,17 29,40 40,25 19,78
(using "DAO 3.6 object library")
Equipment dual core 2,4GHz Pentium, 3Gb Ram. Tests executed on a local drive (though a short test on a network drive left the impression that it doubles the amount of time).

What really made me wonder are two things:
1. jrbarnett is way slower, although his machine is faster
2. the difference in the factor between me and jrbarnett

any ideas?

Rincewind

 

[JerryKlmns]

That's from network traffic and extra RAM plus HDD % free space.

 

[jrbarnett]

Moved it to a local drive and re ran

Equivalent of run 4
0.45
7.76
17.13

Access XP with SP3 on XP Pro SP2. Database in Access 2000 format.

Thus the main factor is local vs network drive.

John

 

[Rincewind123456]

I don't think that the difference between 2 and 3Gbs of RAM matters for that application.

As jrbarnett pointed out it's mainly local vs network drive - interesting is that this seems to hurt the SQL Insert more than the Recordset.

Anyway, regaring PieSchie's original question - no one can think of another way to put the array data into a table?