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

How fast can we add 1000 or other number of records?

P: n/a
This takes about 2 seconds on my rather obsolete machine:

Option Explicit

' Test is a simple JET Table with four fields
' ID -> autonumber primary key
' Field1 -> Integer (maps to VBA long)
' Field2 -> Text (50)
' Field3 -> DateTime

Sub AddRecords()
Dim r As ADODB.Recordset
Dim z As Long
Debug.Print Now() ' 2005-03-17 19:44:22
Set r = New ADODB.Recordset
With r
.CursorLocation = adUseClient
.Open _
"SELECT Field1, Field2, Field3 FROM Test WHERE False", _
CurrentProject.Connection, _
adOpenKeyset, _
adLockBatchOptimistic
.ActiveConnection = Nothing
For z = 0 To 999
r.AddNew Array(0, 1, 2), Array(z, Chr$(z Mod 256), Now())
Next z
.ActiveConnection = CurrentProject.Connection
.UpdateBatch
End With
Debug.Print Now() ' 2005-03-17 19:44:24
End Sub

Do you have a faster way (not including appending from another table, to
which the records were added previously) to add 1000 records? If so,
please, tell us about it.

--
--
Lyle
--
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Thu, 17 Mar 2005 19:52:54 -0500, Lyle Fairfield <ly******@yahoo.ca>
wrote:

What is the purpose of the routine? Quickly adding test records?
If so, perhaps you'd allow appending from same table: add one record.
Then, in a loop run an append query to double up the records until the
desired number of records has been exceeded. That seems the only way I
can see to speed this up: let SQL do what it's good at, with set-based
instructions.

-Tom.

This takes about 2 seconds on my rather obsolete machine:

Option Explicit

' Test is a simple JET Table with four fields
' ID -> autonumber primary key
' Field1 -> Integer (maps to VBA long)
' Field2 -> Text (50)
' Field3 -> DateTime

Sub AddRecords()
Dim r As ADODB.Recordset
Dim z As Long
Debug.Print Now() ' 2005-03-17 19:44:22
Set r = New ADODB.Recordset
With r
.CursorLocation = adUseClient
.Open _
"SELECT Field1, Field2, Field3 FROM Test WHERE False", _
CurrentProject.Connection, _
adOpenKeyset, _
adLockBatchOptimistic
.ActiveConnection = Nothing
For z = 0 To 999
r.AddNew Array(0, 1, 2), Array(z, Chr$(z Mod 256), Now())
Next z
.ActiveConnection = CurrentProject.Connection
.UpdateBatch
End With
Debug.Print Now() ' 2005-03-17 19:44:24
End Sub

Do you have a faster way (not including appending from another table, to
which the records were added previously) to add 1000 records? If so,
please, tell us about it.

--
--
Lyle


Nov 13 '05 #2

P: n/a
Tom van Stiphout wrote:
On Thu, 17 Mar 2005 19:52:54 -0500, Lyle Fairfield <ly******@yahoo.ca>
wrote:

What is the purpose of the routine? Quickly adding test records?


Scheduling or time tabling applications (for groups) may add a
considerable number of records to a schedule or time table..

A record might be appended as

EventID
GroupID
LeaderID
PlaceID
DateTime (or DateTimeID)
.... OtherIDs

The record establishes the event.

Groups, Leaders, Places, DateTimes can be combined to describe
(g * l * p * d) events.

Rules may be established. One could be
"Maximum number of events for a leader at one time = 1"

To achieve our goals in scheduling we may wish to traverse our set of
events multiple times after creating it, modifying or confirming data
each time in keeping with rules, other records or with summative data
based on the whole or part of the set and calculated after each scan.

SQL may not be an efficient technology for adding the records; the data
in each record will vary (as it does in my example).

SQL may not be an efficient technology for modifying the data; at least
in my mind the notion of "update" implies some permanence. I try not to
write temporary changes to a table, especially if I may modify that data
within milliseconds.

By using the recordset I interact with the table twice only, firstly to
get the structure, (then the connection is released) and secondly to
save the results of my calculations, after my calculations are complete.

--
--
Lyle
--
Nov 13 '05 #3

P: n/a
Lyle,

How ya doin'?

I don't see how the described scenario could work with just SQL on Jet
tables, mutating data, inconsistent updates, et cetera, *unless* you
use virtual recordsets acted upon by virtual triggers.

For example, on Oracle and other big db engines, a before row trigger
may perform actions which could impact multiple related rows and cause
mutating triggers resulting in inconsistant data unless the before row
trigger maintains one or more (globay) arrays used by an after
statement to process away the inconsistencies.

Some of these problems can be eliminated by controlling exactly what
happens when, but then the SQL engine optimization(s) may be lost and
the events take more time to complete anyway. (I am pretty sure
you've considered this.)

Another question might be, does this actually have to happen very
fast, or must the user perception be for speed?

Don't know whether any of these thoughts are useful, but HTH.

Elaine
On Thu, 17 Mar 2005 22:02:23 -0500, Lyle Fairfield <ly******@yahoo.ca>
wrote:
Tom van Stiphout wrote:
On Thu, 17 Mar 2005 19:52:54 -0500, Lyle Fairfield <ly******@yahoo.ca>
wrote:

What is the purpose of the routine? Quickly adding test records?


Scheduling or time tabling applications (for groups) may add a
considerable number of records to a schedule or time table..

A record might be appended as

EventID
GroupID
LeaderID
PlaceID
DateTime (or DateTimeID)
... OtherIDs

The record establishes the event.

Groups, Leaders, Places, DateTimes can be combined to describe
(g * l * p * d) events.

Rules may be established. One could be
"Maximum number of events for a leader at one time = 1"

To achieve our goals in scheduling we may wish to traverse our set of
events multiple times after creating it, modifying or confirming data
each time in keeping with rules, other records or with summative data
based on the whole or part of the set and calculated after each scan.

SQL may not be an efficient technology for adding the records; the data
in each record will vary (as it does in my example).

SQL may not be an efficient technology for modifying the data; at least
in my mind the notion of "update" implies some permanence. I try not to
write temporary changes to a table, especially if I may modify that data
within milliseconds.

By using the recordset I interact with the table twice only, firstly to
get the structure, (then the connection is released) and secondly to
save the results of my calculations, after my calculations are complete.

--
--
Lyle


Nov 13 '05 #4

P: n/a
I'm not sure about JET, but the fastest way I've found to add a batch of
records to SQL Server is with an INSERT querydef with parameters for the
values to add. Loop, set the parameters, and execute.

On Thu, 17 Mar 2005 19:52:54 -0500, Lyle Fairfield <ly******@yahoo.ca> wrote:
This takes about 2 seconds on my rather obsolete machine:

Option Explicit

' Test is a simple JET Table with four fields
' ID -> autonumber primary key
' Field1 -> Integer (maps to VBA long)
' Field2 -> Text (50)
' Field3 -> DateTime

Sub AddRecords()
Dim r As ADODB.Recordset
Dim z As Long
Debug.Print Now() ' 2005-03-17 19:44:22
Set r = New ADODB.Recordset
With r
.CursorLocation = adUseClient
.Open _
"SELECT Field1, Field2, Field3 FROM Test WHERE False", _
CurrentProject.Connection, _
adOpenKeyset, _
adLockBatchOptimistic
.ActiveConnection = Nothing
For z = 0 To 999
r.AddNew Array(0, 1, 2), Array(z, Chr$(z Mod 256), Now())
Next z
.ActiveConnection = CurrentProject.Connection
.UpdateBatch
End With
Debug.Print Now() ' 2005-03-17 19:44:24
End Sub

Do you have a faster way (not including appending from another table, to
which the records were added previously) to add 1000 records? If so,
please, tell us about it.

--
--
Lyle


Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.