Stored Procedures

[harsha jagasia]

Hi,
I am coding with Active server pages and SQL server 7.0.
If we were to have too many stored procedures what kind of a performance asvantage/distadvantage would that pose.
With stored procedures fewer parameters have to be passed in,
the SQL statements are prevalidated and compiled, and the amount of data sent over a network is smaller.
However if there are too many stored procedures would that pose a performance disdvantage in any manner.
Can this cause frquent timeouts and if so is there anyway of circumventing the problem.
Please help!
Thanks
Harsha Jagasia

[chris]

Actually, your performance would be increased. The processing is done by SQL, so all you will see is a trim ADO recordset. The proc's cache the query plan and make the process/transaction time much lighter than performing Dynamic SQL within ASP. ASP 3.0 does offer some added functionality with COM+ components, but you will have to be on Windows 2000. A single SQL Server 7 stored proc can have a max of 1,024 input parameters. I haven't ever needed that many yet!


------------
harsha jagasia at 7/13/00 10:38:13 AM

Hi,
I am coding with Active server pages and SQL server 7.0.
If we were to have too many stored procedures what kind of a performance asvantage/distadvantage would that pose.
With stored procedures fewer parameters have to be passed in,
the SQL statements are prevalidated and compiled, and the amount of data sent over a network is smaller.
However if there are too many stored procedures would that pose a performance disdvantage in any manner.
Can this cause frquent timeouts and if so is there anyway of circumventing the problem.
Please help!
Thanks
Harsha Jagasia