Are stored procedures faster for simple queries

Question

If I am executing a stored procedure for basic queries, such as:

SELECT ColA, ColB FROM MyTable WHERE ID = 123;
SELECT * FROM MyTable,OtherTable WHERE MyTable.ID = OtherTable.ID ORDER BY CreatedAt desc

Is there any benefit to converting those to a stored procedure if that happen frequently? When is it better to use a stored proc? When should I not use a stored proc?

What kind of optimizations, if any, happen when you create a stored proc? (which would help me understand when/why I should use them)

If it makes any differences, my database is an MS SQL 2005 db.

Answer 1

See http://stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-moder

Answer 2

Reasons for using stored procedures:

• Reduce network traffic -- you have to send the SQL statement across the network. With sprocs, you can execute SQL in batches, which is also more efficient.
• Caching query plan -- the first time the sproc is executed, SQL Server creates an execution plan, which is cached for reuse. This is particularly performant for small queries run frequently.
• Ability to use output parameters -- if you send inline SQL that returns one row, you can only get back a recordset. With sprocs you can get them back as output parameters, which is considerably faster.
• Permissions -- when you send inline SQL, you have to grant permissions on the table(s) to the user, which is granting much more access than merely granting permission to execute a sproc
• Separation of logic -- remove the SQL-generating code and segregate it in the database.
• Ability to edit without recompiling -- this can be controversial. You can edit the SQL in a sproc without having to recompile the application.
• Find where a table is used -- with sprocs, if you want to find all SQL statements referencing a particular table, you can export the sproc code and search it. This is much easier than trying to find it in code.
• Optimization -- It's easier for a DBA to optimize the SQL and tune the database when sprocs are used. It's easier to find missing indexes and such.
• SQL injection attacks -- properly written inline SQL can defend against attacks, but sprocs are better for this protection.