Prepared Property Example (ADO)


		Contents Index Topic Contents

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 = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	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