Sql-server – SET ANSI_NULLS – Performance of Stored Procedures

performancesql serverstored-procedures

Does setting ANSI_NULLS ON/OFF affect the performance of stored procedures?

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[TestProcedure]

Best Answer

This does not affect the performance of stored procedures directly, but in general it can affect performance indirectly, since SET settings used at execution time can influence the plan that is chosen. Two different users, one with the setting on, and one with the setting off, will get their own copies of the plans, and they may not even be the same plan (depending on other variables at runtime, like parameter values). See Erland Sommarskog's post, Slow in the Application, Fast in SSMS? On top of differently performing queries, this can lead to potentially different performance and will create two different plans in the cache, wasting memory.

Eventually, you won't even have the ability to set ANSI_NULLS to OFF; see the documentation:

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

So, you should already be in the habit of always setting it to ON. If it's just always on, then it won't be an issue.

Related Solutions

SQL Server – Separate Stored Procedures for Inserts and Updates?

As far as I understand it you are not actually talking about an UPSERT here just combining two different CRUD operations in one stored procedure.

CREATE PROC InsertOrUpdateYourTable @Id int = NULL OUTPUT,
                                    @Foo INT,
                                    @Bar VARCHAR(10)
AS
    IF @Id IS NULL
      BEGIN
          INSERT INTO YourTable
                      (Foo,
                       Bar)
          VALUES      (@Foo,
                       @Bar)

          SET @Id = SCOPE_IDENTITY()
      END
    ELSE
      BEGIN
          UPDATE YourTable
          SET    Foo = @Foo,
                 Bar = @Bar
          WHERE  Id = @Id
      END

The benefit I see to this is that you don't have to maintain two separate parameter lists if the table structure changes. The disadvantage is that the single stored procedure now has two responsibilities and is somewhat less easy to understand.

I would generally opt for separating them into two stored procedures.

RE: "Can you elaborate how an upsert will look"

CREATE PROC UpsertYourTable
@Id int,
@Foo int,
@Bar varchar(10)
AS
MERGE YourTable WITH (HOLDLOCK)  AS T
        USING ( VALUES ( @Id, @Foo, @Bar ) ) 
              AS source ( Id, Foo, Bar)
        ON ( T.Id = source.Id )
        WHEN MATCHED 
            THEN 
        UPDATE SET
                Foo = source.Foo ,
                Bar = source.Bar
        WHEN NOT MATCHED 
            THEN    
        INSERT  (Id, Foo , Bar)
               VALUES 
               (@Id, @Foo , @Bar);

This assumes that Id is no longer an IDENTITY column. The reason for using HOLDLOCK is explained here.

SQL Server – Impact of Raiserror on Stored Procedure Performance

I would not worry about performance in this situation.
I would go with raising an error for 2 reasons:

You quit earlier (when you find out user does not exist), and make a clear message.
IMHO raising an error is more readable and makes your code clearer.

Best Answer

Related Solutions

SQL Server – Separate Stored Procedures for Inserts and Updates?

SQL Server – Impact of Raiserror on Stored Procedure Performance

Related Question