I am using Oracle Provider and i did a little test to see the performance
Dim rsRetour As adodb.Recordset
Dim cmdReturn As adodb.Command
'Dim blnMustCloseConnection As Boolean
On Error GoTo RunSpRetRsError
'blnMustCloseConnection = False
Set m_conADOConnection = r_conADOConnection
' Créer les objets
Set rsRetour = CreateObject("ADODB.Recordset")
Set cmdReturn = CreateObject("ADODB.Command")
' Initialiser la commande
'cmdReturn.ActiveConnection = GetADOConnection()
cmdReturn.ActiveConnection = m_conADOConnection
cmdReturn.CommandText = v_strNomProcedure
cmdReturn.CommandType = adCmdStoredProc
If g_typDatabase = SQLServer Then
CollectParamsSQLServer cmdReturn, v_argParams
ElseIf g_typDatabase = Oracle Then
cmdReturn.Properties.Item("PLSQLRSET") = 1
CollectParamsOracle cmdReturn, v_argParams
End If
' Créer le recordset en READ-ONLY
' On met le update criteria seulement sur la clé pour éviter des conflits
rsRetour.CursorLocation = adUseClient
'rsRetour.Properties("Update Criteria") = adCriteriaKey
Dim intCpt As Integer
Dim ddtDebut As Variant
Dim ddtFin As Variant
ddtDebut = Time
For intCpt = 0 To 1000 Step 1
cmdReturn.Execute
Next
ddtFin = Time
Next
When i do ddtFin - ddtDebut here are my result for the same stored proc:
- Oracle: 8 sec
- SQLServer: 1.5 sec
It is not because of the database data and the procedure is only a select * from XXX
Is the problem due to the provider, if not what's the cause