Stored Procedures

[sendba]

Hi all

I am a basic question ? Which my developer confused me alot. He says that Microsoft is trying to eliminate Stored Procedures. Is that true.

Will Stored Procedures decrease the performance.

Please advice

Thanks
sen

 

[gmmastros]

Perhaps you should remind your developer that today is "Ground Hog Day", not "April Fools".

Stored procedure are NOT going away. In fact, stored procedures speed things up. They do not decrease performance.

With that being said, it's also true that if a stored procedure isn't coded properly, your performance won't be as good as it could.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[onpnt]

I think your developer is smoking something

He says that Microsoft is trying to eliminate Stored Procedures

This seems far fetched to me. There are far too many benefits to SP's. SP have changed in capabilities greatly in 2005 though.

Will Stored Procedures decrease the performance

Yes/No. Anything put together poorly will decrease performance. Anything put together well possibly show gains in performance if it is the write object for the task

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[ptheriault]

Give this information to your developer...

The benefits of using stored procedures are...

Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.

Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.

Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.

Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.

Microsoft would never get rid of them.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[sendba]

First Let me clear it.

My developer uses the Hibernate open source (JTDS) to generated dynamic SQL.
I have to prove that this dynamic sql is not good to my highter authorities.

Please suggest how to do this

Sen

 

[ptheriault]

You should avoid dymanic SQL at all cost. Here are some reasons why.

Avoid dynamic SQL statements as much as possible. Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan every time at runtime. IF and CASE statements come in handy to avoid dynamic SQL. Another major disadvantage of using dynamic SQL is that it requires users to have direct access permissions on all accessed objects, like tables and views. Generally, users are given access to the stored procedures which reference the tables, but not directly on the tables. In this case, dynamic SQL will not work. Consider the following scenario where a user named 'dSQLuser' is added to the pubs database and is granted access to a procedure named 'dSQLproc', but not on any other tables in the pubs database. The procedure dSQLproc executes a direct SELECT on titles table and that works. The second statement runs the same SELECT on titles table, using dynamic SQL and it fails with the following error:

Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'titles', database 'pubs', owner 'dbo'.

To reproduce the above problem, use the following commands:

sp_addlogin 'dSQLuser'
GO
sp_defaultdb 'dSQLuser', 'pubs'
USE pubs
GO
sp_adduser 'dSQLUser', 'dSQLUser'
GO
CREATE PROC dSQLProc
AS
BEGIN
SELECT * FROM titles WHERE title_id = 'BU1032' --This works
DECLARE @str CHAR(100)
SET @str = 'SELECT * FROM titles WHERE title_id = ''BU1032'''
EXEC (@str) --This fails
END
GO
GRANT EXEC ON dSQLProc TO dSQLuser
GO

Now login to the pubs database using the login dSQLuser and execute the procedure dSQLproc to see the problem.

Performance loss: the execution plan for a dynamic query cannot be cached.

Hard to debug.

The error management becomes more unreliable. There is no easy way to validate the dynamic code or control its effects.

Temporary tables from the main statement cannot be used, unless they are global.

If the algorithm of the main statement has many loops, calculations or slow queries, that time will add up to the time of executing the dynamic code.

Maintenance is difficult because the schema is hard coded in the dynamic code. The main statement is harder to understand than regular code because it is necessary to consider how it affects the dynamic code, without seeing it.

Security can be compromised with SQL injection.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[sendba]

Its not hard coded
It is generated as and when needed.
The developer says it is using SP_executesql.

Please advice

Sen

 

[ptheriault]

you still have the performance and security issues it causes.
For example..If he executes the same dynamic sql statement 15 times in an hour. Then SQL server must generate an execution plan each time. If you execute the same statement from a stored procedure 15 times in 1 hr sql server can use the query plan it generated from the 1st time it was executed.

Consider this too. If you make a schema change the dynamic sql is going to break and your going to have to hunt down all the places where he has used dynamic sql. If you are using stored procedures a simple select from INFORMATION_SCHEMA will be able to tell you all the places that could be affected by the change. Plus if you are using schema binding you would know right away what is dependant on the schema you are changing.

Also,
How are you supposed to take the developers dynamic sql and get a showplan on it? It is a nightmare to try and hunt down performance problems. You would have to open profiler and try and track down what it's doing. That's just not very effective.

Sometimes there is no getting around it and you must use dynamic sql. But it's a last resort and should not be used as a primary option.





- Paul
- If at first you don't succeed, find out if the loser gets anything.