Poor performance with stored procedures

[fake]

Hi there,
i've a problem concerning stored procedures with cppbuilder 6.0, dbexpress and mssql server 2000.

for testing i created the following stored procedure on sql server:

CREATE PROCEDURE sp_CountPatients
@result int OUTPUT,
@p int
AS
SELECT @result = COUNT(*) FROM Patient
WHERE LastName LIKE '%' + CAST(@p AS varchar) + '%'
RETURN
GO

the code to test this procedure:

TSQLStoredProc *sp = new TSQLStoredProc(this);
sp->SQLConnection = CommonDataModule->SQLConnection;
sp->StoredProcName = "sp_CountPatients";
sp->Params->ParamByName("p")->AsInteger = 1 ;
sp->ExecProc();
int t1 = sp->ParamByName("result")->AsInteger;
return t1;

as i realized poor performance by using this stored procedure i did a trace with SQL Profiler which gave me the following result:

RowNumber EventClass TextData
----------- ----------- -------------------------
1 12 DECLARE @SCH CHAR(200) ...
2 12 DECLARE @SCH CHAR(200) ...
3 10 declare @P1 int set @P1=1 ...

The statment in Row 1 and 2 is exactly the same, is very lage (66 Lines) and is executed every time before the stored procedure is executed. The result of this statement gives me the parameters of the stored procedure with informations about data type and so on.

I guessed this is because default the ParamCheck Property of TSQLStoredProcedure is set to true. I did some testing, set sp->ParamCheck = false, so i could avoid the double execution of this statement. But the problem is still present, every time i execute this stored procedure, this statement is executed.

I did some testing with BDE, there is no Statement for the Parameters executed, but i try to find a solution for dbexpress ...

has anyone an idea where this Statement came from and how to disable this ?

Thanks in advance

Michael

[Antaeus]

It occurs because dbExpress component asks our driver to get params from the server. The first time when StoredProcName changes and the second time before execution of the stored procedure.
Read C++Builder help for more information.

[fake]

is there no way to disable this ?
Stored Procedures are getting unusable for us concerning performance, because it takes more time to use the stored procedure with this "overhead" than to use a standard sql-query.

Thanks for information
Michael

[Antaeus]

To increase performance of TSQLStoredProc try to select some procedures in your application you use quite often and assign one stored procedure to one instance of TSQLStoredProc. TSQLStoredProc gets parameters from the server only the first time after StoredProcName was changed.

[fake]

this works/helps,
i set ParamCheck = false, set the params by hand and use only one instance of it.
Stored procedures result in better performance now.

Thank you for info & help
Michael