Unanswered: stored procedures VS embedded programming
Well, I was wondering which solution is faster (stored procedures or embedded programming) I've being done some benchmarks and, it seems the stored procedures are the best option:
INSERT each time a set of 4,000 rows in a table, once using a Stored Procedure ( see the preceding line with the text: "-------- Stored Procedure ------") and then,
without it (see the preceding line with the text: "-------- Without Stored Procedure ------"):
Stored procedures are always faster, because of the following reasons:
1. When the stored procedure is created it is compiled and the plan or the sequence tree is stored in the system table. When the stored procedure is first executed, it is optimized and the final query plan is stored in the procedure cache. the subsequent execution of the stored procedure will use the stored query plan from the procedure cache. Which saves a lot of time.
2. The compiled code is stored in the database, therefore it reduces the network usage each time it is executed.
3. Apart from performance impprovements, stored procedures also help in security issues. Users do not need direct permissions on the tables and columns, they just need execute permission on the stored proceudre.
where as for queries,
1. Compiled and optimized each time they are executed
2. The whole of the sql text has to travel through the network from client to the server
3. Users need select permission on table or selected columns to be able to run a SQL