Slow Addnew Method with SQL Server

[John Kounis]

I recently converted from Access 2000 to an MSDE backend. In this database,
I need to often append records to a table that currently has 750,000 rows.

Using the methods that Microsoft recommends at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthaddnewx.asp, I
wrote the following code to add a new record:

dim rst as new ADODB.Recordset

rst.Open "

", currentproject.connection, adOpenDynamic,
adLockOptimistic ' Takes 5 minutes to open
rst.AddNew
rst!field1 = val1
rst!field2 = val2
rst.Update
rst.close

This is EXTREMELY slow, because the rst.Open statement needs to select
750,000 records.



I would suppose the correct way to do it with a SQL backend would be:

CurrentProject.Connection.Execute "INSERT INTO

(field1, field2,..)
VALUES (val1, val2, ...)"

The problem that I have with this is that I don't want to worry about if the
field values contain """ characters or "'" characters. Also, some fields
have binary data, which I would need to convert to an ASCII equivalent...
all a big hassle.




Another possibility I've considered is the following:

rst.Open "SELECT * FROM

WHERE 1=0", currentproject.connection,
adOpenDynamic, adLockOptimistic
rst.AddNew
rst!field1 = val1
rst!field2 = val2
rst.Update
rst.close

This seems to work, but it seems like an awful kludge to add "1=0" in the
WHERE clause. Is this the only good way?

 

[Douglas J. Steele]

strSQL = "INSERT INTO MyTable (Field1, Field2) " & _
"VALUES (" & val1 & ", " & Chr$(34) & val2 & Chr$(34) & ")"

Here, I'm assuming that Field1 is numeric, and Field2 is text. (The
difference, of course, being the Chr$(34) delimiters on either side of the
value)

If there's a chance that there are going to be " characters in val2, you can
use:

strSQL = "INSERT INTO MyTable (Field1, Field2) " & _
"VALUES (" & val1 & ", " & Chr$(34) & _
Replace(val2, Chr$(34), Chr$(34) & Chr$(34)) & Chr$(34) & ")"

 

[david epsom dot com dot au]

No 'appendonly' flag for ADO. You can do almost the equivalent,
and set the number of returned records (record cache) to be initially
1 (?) or some other small number.

Regarding your kludge suggestion, Jet would accept:
"SELECT * FROM

WHERE FALSE"
-which i think is a nicer kludge.

(david)

I recently converted from Access 2000 to an MSDE backend. In this database,
I need to often append records to a table that currently has 750,000 rows.

Using the methods that Microsoft recommends at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthaddnewx.asp , I

wrote the following code to add a new record:

dim rst as new ADODB.Recordset

rst.Open "

", currentproject.connection, adOpenDynamic,
adLockOptimistic ' Takes 5 minutes to open
rst.AddNew
rst!field1 = val1
rst!field2 = val2
rst.Update
rst.close

This is EXTREMELY slow, because the rst.Open statement needs to select
750,000 records.



I would suppose the correct way to do it with a SQL backend would be:

CurrentProject.Connection.Execute "INSERT INTO

(field1, field2,..)
VALUES (val1, val2, ...)"

The problem that I have with this is that I don't want to worry about if the
field values contain """ characters or "'" characters. Also, some fields
have binary data, which I would need to convert to an ASCII equivalent...
all a big hassle.




Another possibility I've considered is the following:

rst.Open "SELECT * FROM

WHERE 1=0", currentproject.connection,
adOpenDynamic, adLockOptimistic
rst.AddNew
rst!field1 = val1
rst!field2 = val2
rst.Update
rst.close

This seems to work, but it seems like an awful kludge to add "1=0" in the
WHERE clause. Is this the only good way?