Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 17th, 2006, 07:35 PM
Authorized User
 
Join Date: Aug 2004
Location: rogers, ar, USA.
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default Prepared SQL VS. Regular Queries

Guys,

I tried posting this topic in the VBA forum, but got no response. Perhaps someone from this forum can shine some light on this problem.

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

nikotromus
__________________
nikotromus
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Prepared SQL VS. Regular Queries nikotromus Beginning VB 6 1 June 24th, 2006 10:08 AM
Prepared SQL VS. Regular Queries nikotromus Pro VB 6 0 January 17th, 2006 07:37 PM
Prepared SQL VS. Regular Queries nikotromus Access VBA 2 January 14th, 2006 05:56 PM
Anyone Expert in SQL Queries? itHighway HTML Code Clinic 3 June 3rd, 2005 09:57 AM
Anyone Expert in SQL Queries itHighway Classic ASP Basics 2 May 24th, 2005 03:37 AM



All times are GMT -4. The time now is 07:52 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.