Prepared Property Example

This example demonstrates the Prepared property by opening two Command objects—one prepared and one not prepared.

Public Sub PreparedX()

   Dim cnn1 As ADODB.Connection
   Dim cmd1 As ADODB.Command
   Dim cmd2 As ADODB.Command
   Dim strCnn As String
   Dim strCmd As String
   Dim sngStart As Single
   Dim sngEnd As Single
   Dim sngNotPrepared As Single
   Dim sngPrepared As Single
   Dim intLoop As Integer

   ' Open a connection.
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set cnn1 = New ADODB.Connection
   cnn1.Open strCnn

   ' Create two command objects for the same
   ' command -- one prepared and one not prepared.
   strCmd = "SELECT title, type FROM titles ORDER BY type"
   
   Set cmd1 = New ADODB.Command
   Set cmd1.ActiveConnection = cnn1
   cmd1.CommandText = strCmd
      
   Set cmd2 = New ADODB.Command
   Set cmd2.ActiveConnection = cnn1
   cmd2.CommandText = strCmd
   cmd2.Prepared = True
   
   ' Set a timer, then execute the unprepared
   ' command 20 times.
   sngStart = Timer
   For intLoop = 1 To 20
      cmd1.Execute
   Next intLoop
   sngEnd = Timer
   sngNotPrepared = sngEnd - sngStart
   
   ' Reset the timer, then execute the prepared
   ' command 20 times.
   sngStart = Timer
   For intLoop = 1 To 20
      cmd2.Execute
   Next intLoop
   sngEnd = Timer
   sngPrepared = sngEnd - sngStart

   ' Display performance results.
   MsgBox "Performance Results:" & vbCr & _
      "   Not Prepared: " & Format(sngNotPrepared, _
      "##0.000") & " seconds" & vbCr & _
      "   Prepared: " & Format(sngPrepared, _
      "##0.000") & " seconds"
      
   cnn1.Close

End Sub