does it makes sense to do SP for simple queries like select * from users

Question

is it going to be faster if instead of doing

select * from users where id = 1 
or
delete from users where id = 1
or 
select count(*) from users

I would create a SP for it ?

This is a possible duplicate of: http://stackoverflow.com/questions/2734007/when-is-it-better-to-write-ad-hoc-sql-vs-stored-procedures/2734158#2734158, which has some good answers. — Paddy, Jun 10 '10 at 14:05
Or rather, having followed a big duplicate chain, to this one: http://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code — Paddy, Jun 10 '10 at 14:07

score 3 · Accepted Answer · answered Jun 10 '10 at 13:15

Performance-wise, no it doesn't make any difference.

Security-wise, it does made a difference. Using a sproc means you only need to grant execute permissions on the sproc, whereas the non-sproc approach would require permissions to be granted directly on the underlying table(s).

Network-traffic-wise - potential, slight/negligible difference. More applicable to larger statements whereby you either send the entire SQL statement across the wire or send just the sproc call. Pretty neglible overall.

Maintenance-wise - the sproc approach would allow you to (e.g.) tune a query without having to redeploy the whole application.

Something I'd be thinking is parameterising the query instead of using "hardcoded" values within an sql statement to support execution plan reuse.

score 2 · Answer 2 · answered Jun 10 '10 at 13:10

If you're concerned about efficiency, don't do:

Select * From Users

Instead do:

Select column1, column2 From Users

Otherwise, SQL Server needs to do a lookup on all the columns in the Users table.

Personally, I wouldn't put something like this in a stored proc, but some people would, if they are doing all their data access via stored procedures.

score 1 · Answer 3 · answered Jun 10 '10 at 13:09

If you are using SPs for all data access then yes. Otherwise I would not use it. It's never a good idea to have inconsistent behavior especially in source code.

I guess you are asking this question because you think that SPs are faster than inline queries sent by program but starting from sql server 2005 this is no more true as all execution plans are cached.

score 1 · Answer 4 · answered Jun 10 '10 at 13:11

I'm leaning more towards a no on this one but then again I can see scenarios where you may want to do this.

For example, you may wish to implement a layer of security by utilizing stored procedures.

There is no improved plan reuse by using a stored procedure when considering the sample queries you have provided.

does it makes sense to do SP for simple queries like select * from users

4 Answers4