ODBC and Stored Procedures

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 you help,

Steve

your code seem fine..I don't really know C that much..but it's look fine...

Few questions about your SQL
1-to you have an index on the field Analog_Id ?
2-why do you need to call this procedure a lot?
3-is it a big table?
4-how many index is on the table? (remember :too much index slow Upd/Ins/DEL but improve select performance )

a few answer :
1-try to put all of your SQL statements into one batch, so you won't have round trip from and to your server. Put them into a big String Variable then execute the string variable(only once) you might get a faster result
2-try to see if the upate has any triggers...this might slow the process.

Sypher,

Thanks for the help on this issue, here are some answers for your questions.

There is an index on IRISAnalog.
Have to do many inserts/update as part of a full update of the database, whoes data comes from another data source.
Table is 17 columns, with about 10,000 entries.
2 indicies on the table. One for Id, once for subscript (subs).

I have problems trying to use sp_executesql. I belive it is a stored procedure, and from some reason, all stored procedures are causing a resultset to be prepared in my C code. (Even if the procedure has just a return statement)

No triggers.

Once again, thanks for the help.

Steven

instead of this
while(I am looping)
{
rc = SQLExecute(*hstmt);
}

this this
while(I am looping)
{
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]

<String_var> =+ *hstmt
}//while

rc = SQLExecute(<String_var>);

the syntax is not correct but I'm sure you get my point...try to put ALL of you update in one big query then run your Store procedure only once...this way you won't get your slow down ..and since your putting, your update, in a String_var ..it all memry access so it's pretty fast even 10000 times..