Stored procedures vs. parameter binding

Question

I am using SQL server and ODBC in visual c++ for writing to the database. Currently i am using parameter binding in SQL queries ( as i fill the database with only 5 - 6 queries and same is true for retrieving data). I dont know much about stored procedures and I am wondering how much if any performance increase stored procedures have over parameter binding as in parameter binding we prepare the query only once and just execute it later in the program for diferent set of values of variables.

Answer 1

Stored procedures should be more performant for a few reasons:

• Less network traffic - the query is on the DB and you just send a small command to the DB with params vs sending the entire query every time
• The query is pre compiled on the server and can be cached as well by the DB

Another advantage is that you can alter the query on the DB without having to recompile the code. This is an additional layer of abstraction that I find very useful.