Testiruju performance stored procedure po sravneniju s parametrizirovannym query i prostym RS.Open SQLString
Pluchaetsja peredacha SQL zaprosa v vide stroki bez parametrov v metod RS.Open - samyj bystryj metod.
Privozhu resul'taty testa i nizhe samu proceduru testa.
Mozhet kto to posovetuet kak dobit'sja ot stored procedures ili query s parametrami povyshennoj proizvoditel'nosti .. kak eto zajavljaetsja vo vseh uchebnikah.

Test Result (in sec):

200 Cycles Loop :
NOT Prepared Command W/O Parameters : 1.66406300
Prepared Command W/O Parameters : 1.92968800
NOT Prepared Stored Procedure Command WITH Parameters : 1.96875000
NOT Prepared Command WITH Parameters : 0.90625000
Prepared Command with WITH Parameters: 1.52343800
Recordset.Open : 0.56250000

VB code :

' Dim gConn AS ADODB.Connection inicializiruetsja v drugom module

Public Sub SQLTest(Index As Integer)
Dim Rs0 As ADODB.Recordset

Dim Param1 As ADODB.Parameter
Dim Param2 As ADODB.Parameter
Dim Param3 As ADODB.Parameter

Dim Rs As ADODB.Recordset

Dim SqlStr As String
Dim ParamSqlStr As String
Dim ConnStr As String

Dim MeasureParam As Long
Dim TimeParam As Long
Dim StudyParam As Long

Dim MaxLoop As Long
Dim i As Long

Dim StartTime As Single
Dim StopTime As Single

Set Rs = New ADODB.Recordset
Set Param1 = New ADODB.Parameter
Set Param2 = New ADODB.Parameter
Set Param3 = New ADODB.Parameter

MaxLoop = 199 '299

InitConnectionCommand Index

MeasureParam = Int((3 * Rnd) + 1)
TimeParam = Int((3 * Rnd) + 1)
StudyParam = Int((3 * Rnd) + 1)
sc.Response.Write MaxLoop + 1 & " Cycles Loop : <BR>"

SqlStr = "SELECT TResult.TResultID FROM (Study INNER JOIN TGroup ON Study.StudyID = TGroup.StudyID) INNER JOIN (Measure INNER JOIN (TResult INNER JOIN TimePeriodLst ON TResult.TPID = TimePeriodLst.TPID) ON Measure.MeasureID = TResult.MeasureID) ON TGroup.TGroupID = TResult.TGroupID WHERE (((Study.StudyID)=" & StudyParam & ") AND ((TimePeriodLst.TPID)=" & TimeParam & ") AND ((Measure.MeasureID)=" & MeasureParam & "));"
gComm.CommandType = adCmdText
gComm.CommandText = SqlStr

gComm.Prepared = False
StartTime = Timer
For i = 0 To MaxLoop
MeasureParam = Int((3 * Rnd) + 1)
TimeParam = Int((3 * Rnd) + 1)
StudyParam = Int((3 * Rnd) + 1)
SqlStr = "SELECT TResult.TResultID FROM (Study INNER JOIN TGroup ON Study.StudyID = TGroup.StudyID) INNER JOIN (Measure INNER JOIN (TResult INNER JOIN TimePeriodLst ON TResult.TPID = TimePeriodLst.TPID) ON Measure.MeasureID = TResult.MeasureID) ON TGroup.TGroupID = TResult.TGroupID WHERE (((Study.StudyID)=" & StudyParam & ") AND ((TimePeriodLst.TPID)=" & TimeParam & ") AND ((Measure.MeasureID)=" & MeasureParam & "));"
gComm.CommandText = SqlStr
Set Rs = gComm.Execute
Next
StopTime = Timer
PrintElapsedTime "NOT Prepared Command W/O Parameters : ", StartTime, StopTime

gComm.Prepared = True
StartTime = Timer
For i = 0 To MaxLoop
MeasureParam = Int((3 * Rnd) + 1)
TimeParam = Int((3 * Rnd) + 1)
StudyParam = Int((3 * Rnd) + 1)

SqlStr = "SELECT TResult.TResultID FROM (Study INNER JOIN TGroup ON Study.StudyID = TGroup.StudyID) INNER JOIN (Measure INNER JOIN (TResult INNER JOIN TimePeriodLst ON TResult.TPID = TimePeriodLst.TPID) ON Measure.MeasureID = TResult.MeasureID) ON TGroup.TGroupID = TResult.TGroupID WHERE (((Study.StudyID)=" & StudyParam & ") AND ((TimePeriodLst.TPID)=" & TimeParam & ") AND ((Measure.MeasureID)=" & MeasureParam & "));"
gComm.CommandText = SqlStr
Set Rs = gComm.Execute
Next
StopTime = Timer
PrintElapsedTime "Prepared Command W/O Parameters : ", StartTime, StopTime

ParamSqlStr = "pz_test"
gComm.CommandType = adCmdStoredProc
gComm.CommandText = ParamSqlStr
gComm.Prepared = False

Set Param1 = gComm.CreateParameter("@StudyID", adInteger, adParamInput, 4, 3)
gComm.Parameters.Append Param1
Set Param2 = gComm.CreateParameter("@MeasureID", adInteger, adParamInput, 4, 3)
gComm.Parameters.Append Param2
Set Param3 = gComm.CreateParameter("@TPID", adInteger, adParamInput, 4, 3)
gComm.Parameters.Append Param3


StartTime = Timer
For i = 0 To MaxLoop
Param1.Value = Int((3 * Rnd) + 1)
Param2.Value = Int((3 * Rnd) + 1)
Param3.Value = Int((3 * Rnd) + 1)
Set Rs = gComm.Execute
Next
StopTime = Timer

PrintElapsedTime " NOT Prepared Stored Procedure Command WITH Parameters : ", StartTime, StopTime


ParamSqlStr = "SELECT TResult.TResultID FROM (Study INNER JOIN TGroup ON Study.StudyID = TGroup.StudyID) INNER JOIN (Measure INNER JOIN (TResult INNER JOIN TimePeriodLst ON TResult.TPID = TimePeriodLst.TPID) ON Measure.MeasureID = TResult.MeasureID) ON TGroup.TGroupID = TResult.TGroupID WHERE (((Study.StudyID)=?) AND ((TimePeriodLst.TPID)=?) AND ((Measure.MeasureID)=?));"
gComm.CommandType = adCmdText
gComm.CommandText = ParamSqlStr

gComm.Prepared = False

StartTime = Timer
For i = 0 To MaxLoop
Param1.Value = Int((3 * Rnd) + 1)
Param2.Value = Int((3 * Rnd) + 1)
Param3.Value = Int((3 * Rnd) + 1)

Set Rs = gComm.Execute
Next
StopTime = Timer
PrintElapsedTime " NOT Prepared Command WITH Parameters : ", StartTime, StopTime

gComm.Prepared = True

StartTime = Timer
For i = 0 To MaxLoop
Param1.Value = Int((3 * Rnd) + 1)
Param2.Value = Int((3 * Rnd) + 1)
Param3.Value = Int((3 * Rnd) + 1)

Set Rs = gComm.Execute
Next
StopTime = Timer
PrintElapsedTime "Prepared Command with WITH Parameters: ", StartTime, StopTime

Rs.Close
StartTime = Timer
For i = 0 To MaxLoop
MeasureParam = Int((3 * Rnd) + 1)
TimeParam = Int((3 * Rnd) + 1)
StudyParam = Int((3 * Rnd) + 1)

SqlStr = "SELECT TResult.TResultID FROM (Study INNER JOIN TGroup ON Study.StudyID = TGroup.StudyID) INNER JOIN (Measure INNER JOIN (TResult INNER JOIN TimePeriodLst ON TResult.TPID = TimePeriodLst.TPID) ON Measure.MeasureID = TResult.MeasureID) ON TGroup.TGroupID = TResult.TGroupID WHERE (((Study.StudyID)=" & StudyParam & ") AND ((TimePeriodLst.TPID)=" & TimeParam & ") AND ((Measure.MeasureID)=" & MeasureParam & "));"

Rs.Open SqlStr, gConn
Rs.Close
Next
StopTime = Timer
PrintElapsedTime "Recordset.Open : ", StartTime, StopTime

Set Rs = Nothing
Set Param1 = Nothing
Set Param2 = Nothing
Set Param3 = Nothing

End Sub


Regards, Far Stringer

Eshe dobavka .. text stored procedure

create procedure dbo.pz_test
@StudyID int,
@TPID int,
@MeasureID int
as

SELECT TResult.TResultID FROM (Study INNER JOIN TGroup ON Study.StudyID = TGroup.StudyID) INNER JOIN (Measure INNER JOIN (TResult INNER JOIN TimePeriodLst ON TResult.TPID = TimePeriodLst.TPID) ON Measure.MeasureID = TResult.MeasureID) ON TGroup.TGroupID = TResult.TGroupID WHERE (((Study.StudyID)=@StudyID) AND ((TimePeriodLst.TPID)=@TPID) AND ((Measure.MeasureID)=@MeasureID))
GO


Regards, Far Stringer