Stored Procs vs Dynamic SQL
In DeKlarit's booth at TechEd I had to talk more than I wanted about the famous Stored Procs vs Dynamic SQL debate.
People still thinks Stored Procedures are faster, even if there is much evidence that shows otherwise. Fortunately, when they go and ask the LinQ for SQL/Entities team they get the same answer than they get from me. They are not.
To add more evidence, a couple of weeks ago I was in Redmond in a meeting with the ADO.NET team, and a Tech Lead from that team wrote the code below and said 'this is the fastest way to execute this SQL sentence with the .NET framework today'.
SqlCommand cmd = sqlConnection.CreateCommand();
cmd.CommandText = @"
SELECT Sales PersonId, FirstName, HireDate
FROM SalesPerson as sp
INNER JOIN Employee AS e ON sp.SalesPersonID = e.EmployeeID
INNER JOIN Contact AS c ON e.EmployeeID = c.ContactID
WHERE e.HireDate < @date";
cmd.Parameters.AddWithValue("@date", date");
DbDataReader r = cmd.ExecuteReader();
(if it looks similar to the query shown here is just a coincidence ;) )
Why is this _faster_ than a stored procedure? Because the SQL Server engine could select a better execution plan depending on the value of the @date parameter.
Even in that room there was people that were surprised.