Poor performance calling a stored procedure

[WebNickson]

Hi,

I've a performance problem when calling an oracle stored procedure for a record insertion. Below is my code :

For n = 1 to 5
...Some other code...
Set Command1 = Server.CreateObject("ADODB.Command&quot
With Command1
.ActiveConnection = CONN_STRING
.CommandText = "SP_ABC"
.CommandType = adCmdStoredProc
.CommandTimeout = 0
.Prepared = true
.Parameters.Append Command1.CreateParameter("RV_FROMCODE", 200, 1,6,strTemp)
.Parameters.Append Command1.CreateParameter("RV_RESULT", 200, 1,6)
.Execute()
End With
strResult = Command1.Parameters("RV_RESULT&quot
...Some other code...
Next

The code is kind of inefficient like I am creating the command obj in each loop!

Could someone advise me on how to optimize the code?

rgds,
Nickson

 

[SpiderBear6]

Why not create the command object outside the loop and then just reset the values of the command object inside the loop just before you execute it.

--------------------------------------
"We are star-stuff. We are the universe made manifest trying to figure itself out."
-- Delenn in Babylon 5 - "A Distant Star"

 

[WebNickson]

Actually, I did try that but I got this error which I dun understand at all.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC driver for Oracle][Oracle]ORA-06550: line 1, column 218: PLS-00103: Encountered the symbol &quot;>&quot; when expecting one of the following: ( - + mod not null others <an identifier> <a double-quoted delimited-identifier> <a bind variable> avg count current exists max min prior sql stddev sum variance execute forall time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string>


rgds,
Nickson

 

[BG12424]

Why not create a parameter collection and pass the collection to the command object?


regards,
Brian

http://www.gainesconsulting.com

 

[WebNickson]

Brian, could you explain in greater detail? Are you saying this :

If n = 1 Then
Command1.Parameters.Append Command1.CreateParameter (&quot;RV_FROMCODE&quot;, 200, 1,6)
Command1.Parameters.Append Command1.CreateParameter(&quot;RV_RESULT&quot;, 200, 1,6)
End If
Command1.Parameters(&quot;RV_FROMCODE&quot = strTemp

fyi, it took me only 5s to execute the sp for 6 times but 30s with the above mentioned asp code for the same values.

One more think I cannot understand is why I could not bring the command object creation out of the loop??

rgds,
Nickson

 

[BG12424]

For every iteration you are resetting all the information again, which you don't need to do. Instead, just set up your parameters within a parameters collection (see link below). This will shorten the round trip you are performing in your for loop.

I think that it will be best explained by referencing this website:

http://www.devguru.com/technologies/ado/quickref/command_parameterscollection.html

regards,
Brian

http://www.gainesconsulting.com