problem with stored procedure in access project
-
I slightly modified your code to use
MyCn = CurrentProject.ActiveConnection
I created an Empty SP with no parameteres and only a RETURN statement
Performed code and SQL Server on the same server:
Loops : 1 RPC : 10 SQLBat : 0 -100 %
Loops : 10 RPC : 30 SQLBat : 20 -33 %
Loops : 100 RPC : 150 SQLBat : 270 80 %
Loops : 1000 RPC : 1613 SQLBat : 2413 50 %
Loops : 10000 RPC : 16093 SQLBat : 23324 45 %
Loops : 25000 RPC : 38836 SQLBat : 58934 52 %
In the next post I will post the results for an empty SP with a single INT parameter.
Rich
-
I remodified the code (without doing the param thing Noeld proposed).
modifications :
--this clears the cache and reinsert the cacheable plans.
MyCn.Execute ("DBCC DROPCLEANBUFFERS")
MyCn.Execute ("DBCC FREEPROCCACHE")
MyCn.Execute "EXEC dbo.Test"
MyCmd.Execute
new results :
Loops : 1 RPC : 0 SQLBat : 16 100 %
Loops : 10 RPC : 0 SQLBat : 15 100 %
Loops : 100 RPC : 63 SQLBat : 109 73 %
Loops : 1000 RPC : 594 SQLBat : 922 55 %
Loops : 10000 RPC : 5750 SQLBat : 10625 85 %
Loops : 25000 RPC : 14156 SQLBat : 22641 60 %
-
Results with a single INT parameter in an empty SP:
Loops : 1 RPC : 20 SQLBat : 0 -100 %
Loops : 10 RPC : 20 SQLBat : 30 50 %
Loops : 100 RPC : 171 SQLBat : 260 52 %
Loops : 1000 RPC : 1773 SQLBat : 2593 46 %
Loops : 10000 RPC : 17535 SQLBat : 29192 66 %
Loops : 25000 RPC : 42572 SQLBat : 63281 49 %
The INT parameter was set to the value of the loop counter for each call to test the parameterized plan advantage.
The results from the previous (no parameter):
Loops : 1 RPC : 10 SQLBat : 0 -100 %
Loops : 10 RPC : 30 SQLBat : 20 -33 %
Loops : 100 RPC : 150 SQLBat : 270 80 %
Loops : 1000 RPC : 1613 SQLBat : 2413 50 %
Loops : 10000 RPC : 16093 SQLBat : 23324 45 %
Loops : 25000 RPC : 38836 SQLBat : 58934 52 %
Hardly any difference (w or w/o parameter), and only a modest advantage for RPC over the batch. A non trivial SP would likely reveal much closer results, as the call interface becomes less important.
-----------------------
Modified Code
Public Sub x()
Dim MyCmd As ADODB.Command
Dim MyCn As ADODB.Connection
Dim P As ADODB.Parameter
Dim lRPC As Long
Dim lSQLBat As Long
Dim lStart As Long
Dim i As Long
Dim MaxI As Long
'ResetConnection
Set P = New ADODB.Parameter
P.Type = adInteger
P.Name = "@P"
Set MyCn = CurrentProject.AccessConnection
Set MyCmd = New ADODB.Command
MyCmd.CommandText = "dbo.Test1"
MyCmd.CommandType = adCmdStoredProc
MyCmd.ActiveConnection = MyCn
MyCmd.Parameters.Append P
MaxI = 1
While MaxI < 26000
lStart = GetTickCount
For i = 0 To MaxI
P.Value = i
MyCmd.Execute
Next
lRPC = GetTickCount - lStart
lStart = GetTickCount
For i = 0 To MaxI
MyCn.Execute " EXEC dbo.Test1 " & i
Next
lSQLBat = GetTickCount - lStart
If lRPC = 0 Then
Debug.Print "Loops : " & MaxI & vbTab & "RPC : " & lRPC & vbTab & "SQLBat : " & lSQLBat & vbTab & IIf(lSQLBat = 0, " 0 %", "100 %")
Else
Debug.Print "Loops : " & MaxI & vbTab & "RPC : " & lRPC & vbTab & "SQLBat : " & lSQLBat & vbTab & CInt((lSQLBat * 100 / lRPC) - 100) & " %"
End If
If MaxI = 10000 Then
MaxI = 25000
Else
MaxI = MaxI * 10
End If
Wend
Set MyCmd = Nothing
End Sub
-
Did any of this test Evaluated CPU utilization on the server?
I see a lot of round trip timings but not what is going on on the server which is the palce that have to pull everything together.
Just keep in mind that you are trying to "simulate" different scenarios but reality is hard to to match. (unless you are M$
)All in all you have always gotten RPC superiority ( which again may not be terribly better) but if you are on a tight system every bit helps, believe me I've been there!
* Noel -
I'll try to get at work an hour earlier... that way I'll have the whole system to myself so that network and server load won't be an issue.
-
I wanted to go to 1M loops but some employees got here sooner than expected so the test had to stop at 100K :
Loops : 1 RPC : 16 SQLBat : 16 0 %
Loops : 10 RPC : 15 SQLBat : 16 7 %
Loops : 100 RPC : 141 SQLBat : 187 33 %
Loops : 1000 RPC : 1188 SQLBat : 1625 37 %
Loops : 10000 RPC : 11812 SQLBat : 15422 31 %
Loops : 100000 RPC : 119203 SQLBat : 151797 27 %
While the % may not seem big, even on a 2:30 job, RPC saved over 30 secs on the sql batch. If you still think it's not a lot, that means over 16 mins/hour that the server will have of free time to process other queries. This is not a small number if you're on a tight system
.the proc
CREATE PROCEDURE dbo.Test @ID as int
AS
SET NOCOUNT ON
SELECT Name, id from dbo.SysObjects where id = @ID
SET NOCOUNT OFF
GO
the code
Sub x()
Dim MyParam As ADODB.Parameter
Dim MyCmd As ADODB.Command
Dim lRPC As Long
Dim lSQLBat As Long
Dim lStart As Long
Dim i As Long
Dim MaxI As Long
ResetConnection
Set MyCmd = New ADODB.Command
MyCmd.CommandText = "dbo.Test"
MyCmd.CommandType = adCmdStoredProc
Set MyParam = New ADODB.Parameter
MyParam.Direction = adParamInput
MyParam.Name = "ID"
MyParam.Size = 4
MyParam.Type = adInteger
MyParam.Value = -1
MyCmd.Parameters.Append MyParam
MyCmd.ActiveConnection = MyCn
MyCn.Execute ("DBCC DROPCLEANBUFFERS")
MyCn.Execute ("DBCC FREEPROCCACHE")
MyCn.Execute "EXEC dbo.Test -1"
MyCmd.Execute
MaxI = 1
While MaxI < 1100000
lStart = GetTickCount
For i = 0 To MaxI
MyParam.Value = i
MyCmd.Execute
Next
lRPC = GetTickCount - lStart
lStart = GetTickCount
For i = 0 To MaxI
MyCn.Execute "EXEC dbo.Test " & i
Next
lSQLBat = GetTickCount - lStart
If lRPC = 0 Then
Debug.Print "Loops : " & MaxI & vbTab & "RPC : " & lRPC & vbTab & "SQLBat : " & lSQLBat & vbTab & IIf(lSQLBat = 0, " 0 %", "100 %")
Else
Debug.Print "Loops : " & MaxI & vbTab & "RPC : " & lRPC & vbTab & "SQLBat : " & lSQLBat & vbTab & CInt((lSQLBat * 100 / lRPC) - 100) & " %"
End If
' If MaxI = 10000 Then
' MaxI = 25000
' Else
MaxI = MaxI * 10
' End If
Wend
Set MyCmd = Nothing
End Sub
-
This is a great thread, although we are deviating from the main topic.
I think the results return exactly as we would predict: a reasonable advantage for multiple loops or a heavily loaded server.
Practically speaking, this would convince me to use the verbose RPC call method when there are is a big loop or many calls.
For most calls however, I guess it's a matter of preference or style, with the SQL injection caveat thrown in.
Rich
-
I think you forgot one point. If you have a lot of users that can access the same sets of form. There will be a few 10K of call of the same procs over the whole day (or if you have a big website with a homepage that's hungry for data). The time saved will be the same... only on a longer period.
-
This is a nice way to demonstrate things but still the subject is even deeper
When you get called to see a server that has nearly 100% CPU usage is when you appreciate that every bit counts because the effect is exponential! same thing for every improvement you make, it will release pressure on some other queries that are doing heavy lifting and then you get less escallations threfore less locks , therefore more speed, therefore more power for the next job.... etc.
It is nice that some times we can approach to reallity like this but we should always need to take a step back and make sure that we are not fooling ourselves and that the model that we use is close enough
This thread is a keeper !
Thanks for all the fish
* Noel -
Did you had any other more realistic tests in mind??
-
What you did is a good start. Simulation is a BIG topic and with $$$ you can setup many scenarios to see how one interface or the other holds when there is preassure on the server. RPC as it names indicates was created for THAT is a protocol to encapsulate a procedure call across the network (with parameters of course, or at least usually
) a batch will eventually degenerate to the same point but with an extra over head.M$ can setup all those labs with people that do nothing but just that (you do need time and $$)
* Noel -
It's a shame... I have neither
.Ya, ya... I'm trying to slow down
. -
It's a shame... I have neither
Yeah I can Imagine!!!
* Noel
Viewing 13 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply