Looks much better, although the title is a bit misleading.
Also, your 'dynamic sql' isn't quite dynamic. You simply built up a query in C#, but the resulting sql query is not dynamic.
Dynamic queries are about building strings in sql and executing them as strings (which then get executed as if they were actual queries). This is an interesting and thorough article about dynamic sql[^], you should read it.
Anyway, since you've fixed the problematic error I will revote your article. Nice work.
You might want to say *lazy written* say stored procedures do not increase performance. And they do increase performance on many levels (not just measuring the same statement).
But it's not just performance.
Arguments are not convincing:
- vendor lock-in: isn't the same with hand written SQL? Or you do just SELECT INSERT UPDATE DELETE. It's inevitable to reach a sp_getapplock form or another; mutexes (for avoid transactions on whole tables or records), semaphores - basically emulating OS objects and synchronization primitives are what differentiates good SQL systems.
- statements caching: yes and no. You give 2-3 one line examples with the same SQL - but what if you have if this 100-lines-sql else if that 200-lines-sql else 150-lines-sql, and parametrized, where arguments changing etc. you will see the caching is not so clear anymore. SP resides on server and just parameters are passed.
And my 0.02:
- SP controls the flow and enforces logic. It's not just a bunch of SQL
- SP can be written by SQL/business guys that can't do C# or whatever (seriously, do we need a compiler to write queries?)
- SP *and* the synchronization objects resides on server (they are not created and flown from client to server back and forth)
- SP can use other SPs and let the code do just the driving
- queries and scattered all over (best scenario is that we get them together in some file or files), SP can be maintained/deployed very quickly, *without* touching the code
Basically I favor SPs mainly because they enforce users to have a single point of doing something. Throwing SELECTs all over code is at least unpleasant - or you are forced to do the same on client side.
So - arguments are correct, but not convincing to me. And the title is clearly misleading (for newbies, at least).
I agree to all your points ( i have also mentioned those points in the later stages) but as the title says they definitely do not increase performance by any way. All SQL go through the same engine , same caching mechanism.
Please forgive me as I poted after only making a cursory view of your article. When I find time, I will dig into what you've posted and take a look at your degradation scenarios to verify (out of curiosity) whether addressing parameter sniffing will solve issues you have found.
Side Note: In regards to parameter sniffing issues, I have found this to be a significant problem with stored procedures and have made it a habit to create local variables and assign them to passed in parameters. I'm not sure if Microsoft has addressed this problem with a service pack or new version of SQL Server, but I will continue to use this technique until further notice.
CREATE PROCEDURE [dbo].[show_somedata]
-- Add the parameters for the stored procedure here
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- doing this to avoid 'Parameter Sniffing' or 'Bind Variable Peeking' issue
DECLARE @id int
DECLARE @code varchar(4)
SET @id = @pid
SET @code = @pcode
SELECT * FROM sometable WHERE id = @id AND code = @code
Stored Procedures are pre-compiled only upto version 6.5 (ie in year 2000)
(This information is included in SQL Server Certification too)
But as Koirala said still 90% people believe it is faster and write more code on SP.
I can vote for stored procedure only on iteration blocks where SP can reduce the round trip network traffic.
Still SP is too difficult to debug/manage and using EF / ORM reduces the requirement for INSERT/UPDATE SQLS.. Less code more easier management..
Again SPs make the code less object oriented, not TFS friendly...I am stopping