Best way to INSERT

[Luis]

Which of the following two examples is the faster/better way to do a
sql insert from an asp page?

SQLINSERT = "INSERT INTO test ( fname ) " _
& "VALUES (" _
& " '" & fname & "' " _
& ")"

or

SQLINSERT="INSERT INTO test (fname) "
SQLINSERT=SQLINSERT & "VALUES (
SQLINSERT=SQLINSERT & "'" & fname & "' & )"

Is the second method slower than the first?

(Yes, I know, a stored procedure would be even better...)

 

[William Morris]

The human brain processes a 24 frame per second movie as continuous motion,
with this I would imagine we're talking about differences of nano-seconds if
any. In an era of multi-processor multi-Ghz servers, I wouldn't worry too
much about it: even on an old 200mhz Pentium II you won't notice a
difference unless the code below is in a thousands-of-iterations loop.

 

[Chris Barber]

You'd be hard pressed to make the code string generation process be anything
but insignificant compared to the time taken to actually run the SQL
statement against the database.

So .. unless you are doing this thousands of times, any method of generating
the string will be acceptable.

Of course if you are generating large strings or doing it a lot of times
then populate each text segment into an array and use the array join
functionality to create the string.
It's been proven over and over again that the array method is about 100
times quicker than any form of string concatenation since it doesn't rely on
very slow string copy and copy methods used by the concatenation operator.
It scales almost linearly whilst concatenation degrades exponentially as the
overall string size increases.

Chris.


Which of the following two examples is the faster/better way to do a
sql insert from an asp page?

SQLINSERT = "INSERT INTO test ( fname ) " _
& "VALUES (" _
& " '" & fname & "' " _
& ")"

or

SQLINSERT="INSERT INTO test (fname) "
SQLINSERT=SQLINSERT & "VALUES (
SQLINSERT=SQLINSERT & "'" & fname & "' & )"

Is the second method slower than the first?

(Yes, I know, a stored procedure would be even better...)

 

[Chris Hohmann]

Which of the following two examples is the faster/better way to do a
sql insert from an asp page?

SQLINSERT = "INSERT INTO test ( fname ) " _
& "VALUES (" _
& " '" & fname & "' " _
& ")"

or

SQLINSERT="INSERT INTO test (fname) "
SQLINSERT=SQLINSERT & "VALUES (
SQLINSERT=SQLINSERT & "'" & fname & "' & )"

Is the second method slower than the first?

(Yes, I know, a stored procedure would be even better...)

I'd go with the first one, since the second one will error out.

 

[Dave Karmens]

Chris, can you show an example of the array method?

 

[Chris Barber]

This is it in pure VB 6.0 format (VBScript is below it):

Option Explicit
Private marrString() As String
Const ARRAY_PREALLOC = 10
Private mlngCurrentIndex As Long

Public Property Get StringValue() As String
ReDim Preserve marrString(mlngCurrentIndex)
StringValue = Join(marrString)
End Property

Public Sub AddText(ByVal pstrText As String)
Dim plngCurrentUBound As Long
plngCurrentUBound = UBound(marrString)
If plngCurrentUBound - mlngCurrentIndex < ARRAY_PREALLOC Then
ReDim Preserve marrString(plngCurrentUBound + ARRAY_PREALLOC)
End If
mlngCurrentIndex = mlngCurrentIndex + 1
marrString(mlngCurrentIndex) = pstrText
End Sub

Public Sub AddTextCRLF(ByVal pstrText As String)
AddText pstrText
AddText vbCrLf
End Sub

Private Sub Class_Initialize()
mlngCurrentIndex = 0
ReDim Preserve marrString(0)
End Sub

Private Sub Class_Terminate()
ReDim marrString(0)
End Sub

In VBScript:

'Class to encapsulate array generation of strings (no concatenation).
Class FastString

Private ARRAY_PREALLOC
Private marrString()
Private mlngCurrentIndex

Private Sub Class_Initialize
ARRAY_PREALLOC = 50
ReDim marrString(0)
marrString(0) = "" 'Required in VBScript to pre-initialise the array
as strings?
mlngCurrentIndex = 0
End Sub

Public Property Get StringValue()
ReDim Preserve marrString(mlngCurrentIndex)
StringValue = Join(marrString)
End Property

Public Sub AddText(pstrText)
Dim plngCurrentUBound
plngCurrentUBound = UBound(marrString)
If plngCurrentUBound - mlngCurrentIndex < ARRAY_PREALLOC Then
ReDim Preserve marrString(plngCurrentUBound + ARRAY_PREALLOC)
End If
mlngCurrentIndex = mlngCurrentIndex + 1
marrString(mlngCurrentIndex) = pstrText
End Sub

Public Sub AddTextCRLF(pstrText)
AddText pstrText
AddText vbCrLf
End Sub

End Class

Sample Usage in VBScript (ASP):

Dim pobjFS
Set pobjFS = New FastString

'Repeatedly
pobjFS.AddTextCRLF "A line of text"
pobjFS.AddText "Some text without a closing CRLF - use to generate a single
line from multiple text elements"

'Output
Response.Write pobjFS.StringValue

'CleanUp
Set pobjFS = Nothing

NB: The code was tested in a VBS - I am presuming it will work in ASP since
there should be no syntactical issues.

Hope this helps.

Chris.

Chris, can you show an example of the array method?

 

[Egbert Nierop \(MVP for IIS\)]

Which of the following two examples is the faster/better way to do a
sql insert from an asp page?

SQLINSERT = "INSERT INTO test ( fname ) " _
& "VALUES (" _
& " '" & fname & "' " _
& ")"

or

SQLINSERT="INSERT INTO test (fname) "
SQLINSERT=SQLINSERT & "VALUES (
SQLINSERT=SQLINSERT & "'" & fname & "' & )"

Is the second method slower than the first?

(Yes, I know, a stored procedure would be even better...)

It's much better to worry about security and compatibility instead of
concatenating ...
(connection code omitted)

Set cmd = CreateObject("ADODB.Command")
cmd.commandtype = adcmdtext
Set cmd.activeconnection = mydbconn
cmd.commandtext = "INSERT INTO test(fname) VALUES(?)"
cmd.Parameters.append cmd.createparameter(, advarchar, , 50, "Tim's")
cmd.execute , , adExecuteNoRecords

 

[Chris Barber]

Good point.

Good article on SQL Injection:
http://www.securiteam.com/securityreviews/5DP0N1P76E.html

Chris.

message

Which of the following two examples is the faster/better way to do a
sql insert from an asp page?

SQLINSERT = "INSERT INTO test ( fname ) " _
& "VALUES (" _
& " '" & fname & "' " _
& ")"

or

SQLINSERT="INSERT INTO test (fname) "
SQLINSERT=SQLINSERT & "VALUES (
SQLINSERT=SQLINSERT & "'" & fname & "' & )"

Is the second method slower than the first?

(Yes, I know, a stored procedure would be even better...)

It's much better to worry about security and compatibility instead of
concatenating ...
(connection code omitted)

Set cmd = CreateObject("ADODB.Command")
cmd.commandtype = adcmdtext
Set cmd.activeconnection = mydbconn
cmd.commandtext = "INSERT INTO test(fname) VALUES(?)"
cmd.Parameters.append cmd.createparameter(, advarchar, , 50, "Tim's")
cmd.execute , , adExecuteNoRecords

 

[Bob Barrows]

PMFJI.
The array join method is quite simple:
dim ar(2000)
ar(0) = "INSERT INTO test ( fname )"
ar(1) = "VALUES ("
ar(2) = " '" & &
ar(3) = fname
ar(4) = "' )"
SQLINSERT = join(ar," ")
response.write SQLINSERT

HTH,
Bob Barrows

 

[Chris Barber]

Just found a minor bug when using it for something else.

I should have posted:

StringValue = Join(marrString, vbNullString)

So that it doesn't auto add a space before every array entry.

Chris.

This is it in pure VB 6.0 format (VBScript is below it):

Option Explicit
Private marrString() As String
Const ARRAY_PREALLOC = 10
Private mlngCurrentIndex As Long

Public Property Get StringValue() As String
ReDim Preserve marrString(mlngCurrentIndex)
StringValue = Join(marrString)
End Property

Public Sub AddText(ByVal pstrText As String)
Dim plngCurrentUBound As Long
plngCurrentUBound = UBound(marrString)
If plngCurrentUBound - mlngCurrentIndex < ARRAY_PREALLOC Then
ReDim Preserve marrString(plngCurrentUBound + ARRAY_PREALLOC)
End If
mlngCurrentIndex = mlngCurrentIndex + 1
marrString(mlngCurrentIndex) = pstrText
End Sub

Public Sub AddTextCRLF(ByVal pstrText As String)
AddText pstrText
AddText vbCrLf
End Sub

Private Sub Class_Initialize()
mlngCurrentIndex = 0
ReDim Preserve marrString(0)
End Sub

Private Sub Class_Terminate()
ReDim marrString(0)
End Sub

In VBScript:

'Class to encapsulate array generation of strings (no concatenation).
Class FastString

Private ARRAY_PREALLOC
Private marrString()
Private mlngCurrentIndex

Private Sub Class_Initialize
ARRAY_PREALLOC = 50
ReDim marrString(0)
marrString(0) = "" 'Required in VBScript to pre-initialise the array
as strings?
mlngCurrentIndex = 0
End Sub

Public Property Get StringValue()
ReDim Preserve marrString(mlngCurrentIndex)
StringValue = Join(marrString)
End Property

Public Sub AddText(pstrText)
Dim plngCurrentUBound
plngCurrentUBound = UBound(marrString)
If plngCurrentUBound - mlngCurrentIndex < ARRAY_PREALLOC Then
ReDim Preserve marrString(plngCurrentUBound + ARRAY_PREALLOC)
End If
mlngCurrentIndex = mlngCurrentIndex + 1
marrString(mlngCurrentIndex) = pstrText
End Sub

Public Sub AddTextCRLF(pstrText)
AddText pstrText
AddText vbCrLf
End Sub

End Class

Sample Usage in VBScript (ASP):

Dim pobjFS
Set pobjFS = New FastString

'Repeatedly
pobjFS.AddTextCRLF "A line of text"
pobjFS.AddText "Some text without a closing CRLF - use to generate a single
line from multiple text elements"

'Output
Response.Write pobjFS.StringValue

'CleanUp
Set pobjFS = Nothing

NB: The code was tested in a VBS - I am presuming it will work in ASP since
there should be no syntactical issues.

Hope this helps.

Chris.

Chris, can you show an example of the array method?