I understand it is really an time tested debate but I am curious in regards to what others do.
I am inclined to use saved methods for complex reviews, user verification along with other queries that are not your typical Choose, Place, UPDATE, and Remove claims. It's my knowning that SPs are faster simply because they save the execution stack but it is possible to tangable difference for the fundamental claims? The other benefits could be gained for implementing SPs for the everyday fundamental Choose, Place, UPDATE, and Remove claims because it is quite tedius to produce SPS for every fundamental query.
Mostly since you can change or tune them without re-implementing your application. Getting whatever you DB one code is really a positive thing in my opinion. My asp.internet application makes simple calls towards the DB, and all sorts of the DB code is saved within the database instead of being scattered throughout my asp source.
For performance, I believe SP's possess the edge, however i believe that edge continues to be reduced with a few of the more recent versions of SQL server so its most likely not really a deal clincher any longer.
among the issues we've with Sps is ... if it is a "product" .. and also you put many of the core logic inside them , then you're in ways giving your source off to the customer in which you deploy the applying .. you will find ways around for this ....
within our team , we personally pefere to create inline .. please dont scream SQL injection .. that may happen with SPs also .. get all of the data in to the busines logic layer .. and write the logic within the dll or class ...
We don't exactly be aware of data you are dealing with. You are able to decide upon yourself by searching in the Execution Plan.
Execution plans are cached for saved procs as well as for dynamic queries, as lengthy while you use parameters and never string concatenation. There wopuld be very little performance difference for straightforward CRUD queries.
The SQL uses to cache the querys, so subsequent accomplishments does not get put together again, I personally use to check'em with this particular
USE Master GO SELECT UseCounts, RefCounts, CacheObjtype, ObjType, DB_NAME(dbid) as DatabaseName, SQL FROM syscacheobjects WHERE 1=1 AND sql LIKE '%yourSqlSearch%' --AND UseCounts = 1 ORDER BY dbid, UseCounts DESC, objtype GO
For those who have a choose query
SELECT field_list FROM table WHERE id = 2 SELECT field_list FROM table WHERE id = 3
they are cached as different querys, you are able to optimize it utilizing a saved procedure
The "precompiled" benefit was nullified several versions back. If you are using parameterized queries They're virtually equivalent.
I do not quite understand your problem about tedium. You are gonna need to write query, at least one time, in either case. In a single situation it's saved within the database, within the other inside your source code. Possibly more often than once inside your source code, for the way you do DRY.