problem with stored procedure in access project

Question

Post reply

problem with stored procedure in access project

Viewing 15 posts - 16 through 30 (of 43 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I'd still keep the validation though... it's only one more layer of protection, but it can be hacked too.

noeld SSC Guru Points: 96590 More actions · Answer 2

but if you use this syntax:

rs.open "EXEC mySPname " & param1 & ", " & param2 ....

you also get BATCH call and won't enjoy RPC call speed benefits (which may not be a big deal but it may on some cases )

* Noel

Richard Moldwin SSCommitted Points: 1872 More actions · Answer 3

Basically, it's not a factor at all, IMO.

First, these are not queries that are executed thousands of times. We are talking about differences of microseconds here.

Second, what is the evidence that the claims about BATCH vs RPC calls are true for this type of simple EXEC statement? (Just asking, not challenging your statement)

Third, even if what you state is true, what is the evidence that there is a speed difference with a simple EXEC statement. The overhead for this type of statement is about as trivial as you can get.

Fourth, even if there were a slight speed difference, I would argue that the ease of code maintenance and readability outweigh it by a factor of a million to one (IMO, of course).

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Here's your answer for number 2 :

IN QA :

Create proc dbo.Test @Param as int

as

select @Param * 2

go

in VBA :

Sub x()

Dim MyCmd As ADODB.Command

Set MyCmd = New ADODB.Command

MyCmd.CommandText = "dbo.Test"

MyCmd.CommandType = adCmdStoredProc

Dim MyParam As ADODB.Parameter

Set MyParam = New ADODB.Parameter

MyParam.Name = "@Param"

MyParam.Value = 9

MyParam.Size = 4

MyParam.Direction = adParamInput

MyParam.Type = adInteger

MyCmd.Parameters.Append MyParam

MyCmd.ActiveConnection = MyCn

MyCmd.Execute

Set MyParam = Nothing

Set MyCmd = Nothing

MyCn.Execute "EXEC dbo.Test " & 8

End Sub

results from the Profiler :

RPC:Completedexec dbo.Test 9Publisher.adpRemiIDEAL\Remi04004048542005-07-12 12:58:40.570

SQL:BatchCompletedEXEC dbo.Test 8Publisher.adpRemiIDEAL\Remi04004048542005-07-12 12:58:41.960

IN QA :

Drop procedure dbo.Test

3) It won't ever matter for a single execution... Will always matter if ALL your calls are made that way... I can imagine that 1 M calls/day can make some more than a minute difference.

4) I preffer the first method in this case because the parameters and named and typed so it avoids going back and forth to see the code of the proc on the server (especially true if the proc has 15-20 parameters). It also completely avoid the sql injection problem without further validation. My final argument is personal. I created a code generator that creates the whole thing from the private function... to the err handling. I can create the proc access code for about 500 procs/sec. So taking 5 secs to regenerate it once in a while is not a problem for me .

Richard Moldwin SSCommitted Points: 1872 More actions · Answer 5

Remi,

This is really helpful. I love real data! Since you have it all coded up in VBA, could you put both types of calls into a loop of 1,000 or 10,000 or so, and calculate the time difference (i.e use the timer function) for us? If you have the time, I mean .

Thanks,

Rich

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

These test are not really meaningfull because this is on a live server on the network... So if the network is busy or the server busy for some reason the data will be a little off. But as you can see the results are consistently faster for RPC :

Loops : 10 RPC : 31 SQLBat : 47 ± 52%

Loops : 100 RPC : 125 SQLBat : 156 ± 25%

Loops : 1000 RPC : 1047 SQLBat : 1297 ± 24%

Loops : 10000 RPC : 10016 SQLBat : 13422 ± 34%

Loops : 25000 RPC : 26938 SQLBat : 36016 ± 34%

From those numbers the RPC calls would seem to be 34% faster (under extreme reserve )

Richard Moldwin SSCommitted Points: 1872 More actions · Answer 7

Remi,

Thanks again! This is very interesting. From my point of view, it's nice to know that the batch method is only slightly slower than RPC in the long haul. For single calls, it does not seem to matter. I would predict that the difference would decrease substantially as the SP got more complex.

Rich

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Actually I'm thinking that my test doesn't really test the different between RPC and SQL batch... I'll remove the select in the proc and the parameter and try again.

Richard Moldwin SSCommitted Points: 1872 More actions · Answer 9

"Actually I'm thinking that my test doesn't really test the different between RPC and SQL batch... I'll remove the select in the proc and the parameter and try again. "

Then you will just test the difference in the call interface, which is OK. The difference should decrease as the SP grows in complexity (throw in a few joined selects and perhaps an INSERT or two), so you may want to test that also.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

There are too many test cases. Here I'm interested in the difference between RPC and SqlBatch. The execution time of the sp is always gonna vary depending on the use of the server and I don't want that to affect the results... already that I'm doind that on a live server on another pc.. those results will be somewhat questionable.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

This seems to be the most consistant case (except for 100 loops.. should be soemwhat lower than that).

Loops : 1 RPC : 0 SQLBat : 0 0 %

Loops : 10 RPC : 15 SQLBat : 16 7 %

Loops : 100 RPC : 62 SQLBat : 141 127 %

Loops : 1000 RPC : 641 SQLBat : 984 54 %

Loops : 10000 RPC : 6531 SQLBat : 11422 75 %

Loops : 25000 RPC : 17938 SQLBat : 26500 48 %

noeld SSC Guru Points: 96590 More actions · Answer 12

Sorry for my delay in the reply.

Well I think that data is still not realistic. Can you post how did you performed the Loops ?

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Can you do it on a pc that has the server on the same machine?? Would give out much better numbers :

the proc :

Create proc dbo.Test

as

go

the vba code :

Public Declare Function GetTickCount Lib "KERNEL32" () As Long

'MyCn is global to my project

Sub x()

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

MyCmd.ActiveConnection = MyCn

MaxI = 1

While MaxI < 26000

lStart = GetTickCount

For i = 0 To MaxI

MyCmd.Execute

lStart = GetTickCount

For i = 0 To MaxI

MyCn.Execute "EXEC dbo.Test"

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

noeld SSC Guru Points: 96590 More actions · Answer 14

EXACTLY!

The real advantage of the RPC is the ability to reuse parametrized plans.

I would like you to create the parameter once and CHANGE the parameter value on each iteration !

Then Post the results

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 15

Do I still do nothing in the proc (since it takes a plan now)?