|
Subject:
|
Prepared SQL VS. Regular Queries
|
|
Posted By:
|
nikotromus
|
Post Date:
|
1/13/2006 10:42:00 PM
|
Guys,
I tried to write a prepared sql script instead of just executing queries inside of a loop. I timed it, and the prepared script runs over a minute slower than the regular script. I don't understand why it would take longer. I was expecting to save some time.
Here is the prepared statement script:
Dim con As New ADODB.Connection
Dim com As New ADODB.Command
Dim strqry As String
con.ConnectionString = CurrentProject.Connection
con.CursorLocation = adUseServer
con.Mode = adModeReadWrite
con.Open
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
rsData.ActiveConnection = CurrentProject.Connection
rsData.CursorType = adOpenKeyset
strqry = " select q33_1 from test "
rsData.Open (strqry)
Dim SQL_TO_USE As String
SQL_TO_USE = "update test set zip = 'TestThisString' where q33_1 = ?"
com.CommandType = adCmdText
com.Prepared = True
com.CommandText = SQL_TO_USE
com.Parameters.Append com.CreateParameter("value", adVarChar, adParamInput, 50)
Set com.ActiveConnection = con
While Not rsData.EOF
com("value") = rsData("q33_1")
com.Execute
rsData.MoveNext
Wend
con.Close
Here is the regular way of doing it:
Dim strqry As String
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
rsData.ActiveConnection = CurrentProject.Connection
rsData.CursorType = adOpenKeyset
strqry = " select q33_1 from test "
rsData.Open (strqry)
Dim rsAction As ADODB.Recordset
Set rsAction = New ADODB.Recordset
rsAction.ActiveConnection = CurrentProject.Connection
While Not rsData.EOF
strqry = "update test set zip = 'RegularTest' where q33_1 = " & rsData("q33_1") & ""
rsAction.Open (strqry)
rsData.MoveNext
Wend
Any suggestions would be greatly appreciated.
nikotromus
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
1/14/2006 1:17:04 PM
|
Is this an ADP talking to SQL Server? This isn't real clear:
con.ConnectionString = CurrentProject.Connection
con.CursorLocation = adUseServer
con.Mode = adModeReadWrite
con.Open
It looks like you're attempting to open a connection other than the ADO connection already in use by Access. Havn't seen CurrentProject.Connection assigned as a connection string value before. Its already the open ADO connection that Access is using.
Maybe to start:
Set com.ActiveConnection = CurrentProject.Connection
instead of
Set com.ActiveConnection = con
Then at least you're comparing apples to apples.
Bob
|
|
Reply By:
|
nikotromus
|
Reply Date:
|
1/14/2006 5:56:20 PM
|
Bob, I tried it. I took out every reference to the con object and instead made the connection through the command Set com.ActiveConnection = CurrentProject.Connection.
It worked but there was no difference in speed. The prepared scrips still runs one minute slower than executing the non-parsed query script. Oh well. I think this one will just remain a mistery.
nikotromus
|
