February 22, 2008 at 9:49 am
Hello!
I have to work over a production database implemented in SQL 7.0. I'm trying to work out some performance issues, but since I'm also analyzing how it is used, I've found out that it each database has lots of stored procedures (hundreds) that countain just a query, or just a direct update.
I mean, sometimes the SP is directly
create ...
as
insert into table ... values (@param1, @param2)
or contains
select field1, field2, field3 from table where field4 = @param1
Can this issue result into performance issues?
I mean, does wrapping every possible query an application may use into a stored procedure create a serious overhead on the server?
Sure it is better to have some logics located in the database, but shouldn't it be more business logics than just a query?
Thanks,
Roger
February 22, 2008 at 12:20 pm
depends on the number of sprocs you have and how much memory you have.
having all queries in sprocs is great for security reasons, plus SQL will cache the query plans for sprocs, so it doesn't have to recompile or figure out a new plan for your queries.
February 22, 2008 at 1:13 pm
I mean, does wrapping every possible query an application may use into a stored procedure create a serious overhead on the server?
It is actually the opposite. As Adam has stated, using stored procedures allows SQL Server to cache the query plan for reuse, thereby reducing query compile times and lessening the overhead. Also, as Adam mentioned, this also helps out with granting security permissions. Stored procedures can be used to limit and control access to the underlying tables. Permissions can be assigned to the stored procedure instead of the base tables.
There are many other benefits to using stored procedures as well. Most of which, I will not go into here, but one I feel is very important. Limiting all data access to stored procedure calls allows the DBA to make schema changes without having to worry about breaking the application. He/she can then make any schema modifications necessary to support performance, scalability, and new development without the need for someone to fully analyze the application code for dependencies. Something as simple as adding a column could break code when SQL code is allowed to live outside of stored procedures and views.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply