4

If it was a big query would it be faster on the second stored procedure ? 

CREATE PROCEDURE Customers_GetCustomer
      @CustId CHAR(5),
      @type int,
      @search nvarchar
AS
BEGIN
      DECLARE @SQL NVARCHAR(2000)
      SET @SQL = 'SELECT ContactName FROM Customers WHERE CustomerId = '+CONVERT(nvarchar(20), @CustId)
      if(@type = 1)
         BEGIN
          @SQL += 'AND NAME='+@search
         END
      if(@type = 2)
         BEGIN
          @SQL += 'AND ADDRESS='+@search
         END
      EXEC sp_executesql @SQL
END

Versus:

CREATE PROCEDURE Customers_GetCustomer
      @CustId CHAR(5),
      @type int,
      @search nvarchar
AS
BEGIN
      SELECT ContactName FROM Customers 
      WHERE CustomerId = @CustId
      AND (NAME = @search OR @type <> 1)
      AND (ADDRESS = @search OR @type <> 2)
END

What is the better choice between first and second?

Hong Van Vit
  • 2,884
  • 3
  • 18
  • 43
Frédéric Fect
  • 213
  • 3
  • 10
  • 1
    The dynamic SQL has to be parsed on each invocation (although the execution plan might be stashed). The regular SQL can just be compiled directly. If you are running transactions, this could be an issue. If your queries are a little more complicated, then the additional effort for compiling is probably not an issue. – Gordon Linoff Feb 02 '17 at 14:59
  • 1
    Performance differences are negligible but you shouldn't use dynamic sql unless you actually need to because it makes things more difficult to maintain. That leads to the conclusion that it really doesn't much matter. If you need dynamic sql you can't use a standard query. – Sean Lange Feb 02 '17 at 15:19

1 Answers1

2

With a simple query like this, there will be virtually no difference even at 1000s of executions/sec. (Based on my own testing when I had the same question.)

Assuming it's properly parameterised, a complex query will only have the additional overhead of hashing the longer string to match the execution cache.

But I'd suggest testing it yourself, with https://www.brentozar.com/archive/2015/05/how-to-fake-load-tests-with-sqlquerystress/ for example.

T.H.
  • 819
  • 5
  • 4