Re: Stored Procedure Resultsets?

From: Tim (Tim_at_NoSpam)
Date: 03/22/04 

Date: Mon, 22 Mar 2004 20:30:09 +1200

Do you have a trigger that is fired and is inadvertantly producing a
resultset? Does it do Print statements?

- Tim

"SMK" <anonymous@discussions.microsoft.com> wrote in message
news:289DE434-46AF-4BF8-B305-082C08859A32@microsoft.com...
> Hello,
>
> I am having a problem with the performance with when calling stored
> procedures. I have to do a large number of inserts/updates to a table, so
> speed is a big factor. Here is one of my simplified stored procedures I
am
> using for testing:
>
> ALTER PROCEDURE dbo.I7PROCaddAnalog(@p_Analog_Id CHAR(20),
> @p_Analog_Value REAL,
> @p_Analog_Subs INTEGER) as
>
> begin
> SET NOCOUNT ON
>
> UPDATE IRISAnalog SET
> Analog_Value = @p_Analog_Value,
> Analog_Subs = @p_Analog_Subs
> WHERE Analog_Id = @p_Analog_Id
>
> end
>
>
> I am using the C code to call it:
>
> rc = SQLAllocStmt(hdbc, hstmt);
> rc = SQLPrepare(*hstmt, "{call AddAnalog(?,?,?)}", SQL_NTS);
> rc = SQLNumParams (*hstmt, &numParams);
> [BINDPARAMETERS CALL]
>
> while(I am looping)
> {
> rc = SQLExecute(*hstmt);
> rc = SQLFreeStmt(*hstmt, SQL_CLOSE); OR rc = SQLMoreResults(*hstmt);
> }
>
> It seems like I have to close the cursor before I execute another call.
Is
> it fair to say that this is returning a resultset? This seems to be very
> slow.
>
> I have also tried to use the following:
> rc = SQLAllocStmt(hdbc, hstmt);
> rc = SQLPrepare(*hstmt, "UPDATE IRISAnalog SET Analog_Value = ?,
> Analog_Subs = ? WHERE Analog_Id = ?", SQL_NTS);
> rc = SQLNumParams (*hstmt, &numParams);
> [BINDPARAMETERS CALL]
>
> while(I am looping)
> {
> rc = SQLExecute(*hstmt);
> }
>
> This example I do not have to close the cursor. And can quickly execute
> another call.
>
> Is there any way for the stored procedure to not return a resultset (if it
> is returning one) or speed up the efficiency of the stored procedure call?
>
> Thanks for your help,
>
> Steve
>
>
>

Relevant Pages