How can I solve my performance issue in my stored procedure?

Question

How can I get better performance with my sql query in a SP? It has a lot of memory usage.if you look at below my execution pan you will see that :

IF NOT EXISTS(SELECT * FROM Common.[CustomerxxxIds] WHERE xyzType = @xyzType AND CustomerId = @CustomerId)[/code]

has alot of memory usage. How can I reduce that?

ALTER PROCEDURE [Common].[SaveCustomerxxxIds] 
(
    @xyzType    NVARCHAR(128),
    @CustomerId INT,
    @xxxId  INT OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS(SELECT * FROM Common.[CustomerxxxIds] WHERE xxxType = @xxxType AND CustomerId = @CustomerId)
    BEGIN
        INSERT INTO Common.[CustomerxxxIds]
                    ([xxxId]
                    ,[CustomerId]
                    ,[xxxType])
                VALUES
                    (0
                    ,@CustomerId
                    ,@xxxType)
    END

    UPDATE  Common.[CustomerxxxIds]
    SET     [xxxId] = ([xxxId]) + 1
    WHERE   [xxxType] = @xxxType
            AND CustomerId = @CustomerId

    SELECT  @xxxId = xxxId
    FROM    Common.[CustomerxxxIds]
    WHERE   [xxxType] = @xxxType
            AND CustomerId = @CustomerId
END

Answer 1

You can do things to avoid "re-read" of the table to get the output value.

After the INSERT ( INSERT INTO Common.[CustomerxxxIds])

Use SCOPE_IDxxx() to get the newly created surrogate key.

The above will only work for IDxxx columns. From your question, you may not actually have an IDxxx column.

See

https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-idxxx-transact-sql?view=sql-server-2017

.........

with the UPDATE and/or INSERT, you could use OUTPUT functionality to get the value.

https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017

This AVOIDS the last select statement (the "re-read" as I am calling it) to get the desired output value.

Obviously completely removing a SELECT statement will improve performance.

..

Below is a simple but complete Northwind database example of using OUTPUT for INSERT and UPDATE

SELECT 'Before' as Looksie, [ShipperID]
      ,[CompanyName]
      ,[Phone]
  FROM [Northwind].[dbo].[Shippers]

  --
  DECLARE @MyInsertAuditTable table( AuditShipperID INT,  
                           AuditCompanyName nvarchar(40),  
                           AuditPhone nvarchar(24));  
INSERT [Northwind].[dbo].[Shippers] (CompanyName , Phone )
    OUTPUT INSERTED.ShipperID, INSERTED.CompanyName, INSERTED.Phone  
        INTO @MyInsertAuditTable  (AuditShipperID, AuditCompanyName , AuditPhone )

SELECT TOP 1
    --(SELECT MAX(ShipperID) + 1 from dbo.Shippers )
     'Shipper' + LEFT(CONVERT(VARCHAR(38), NEWID()), 12)
    , '(555) 555-5555'
    FROM sys.objects

--Display the result set of the table variable.  
SELECT AuditShipperID, AuditCompanyName, AuditPhone FROM @MyInsertAuditTable;  

  DECLARE @MyUpdateAuditTable table( AuditShipperID INT,  
                           AuditCompanyName nvarchar(40),  
                             AuditOldPhone nvarchar(24),
                           AuditNewPhone nvarchar(24));  

UPDATE [Northwind].[dbo].[Shippers] 

SET Phone = '(777) 555-7777'

OUTPUT inserted.ShipperID,  inserted.CompanyName ,
       deleted.Phone,  
       inserted.Phone
INTO @MyUpdateAuditTable ( AuditShipperID, AuditCompanyName, AuditOldPhone , AuditNewPhone)
FROM [Northwind].[dbo].[Shippers]  shippers
JOIN @MyInsertAuditTable insAudit on shippers.ShipperID = insAudit.AuditShipperID

SELECT * from @MyUpdateAuditTable



SELECT 'After' as Looksie, [ShipperID]
      ,[CompanyName]
      ,[Phone]
  FROM [Northwind].[dbo].[Shippers]

  --

Results

Looksie ShipperID   CompanyName Phone
Before  1   Speedy Express  (503) 555-9831
Before  2   United Package  (503) 555-3199
Before  3   Federal Shipping    (503) 555-9931

..

AuditShipperID  AuditCompanyName    AuditPhone
9               Shipper3C062D46-EEA (555) 555-5555

...

AuditShipperID  AuditCompanyName    AuditOldPhone   AuditNewPhone
9               Shipper3C062D46-EEA (555) 555-5555  (777) 555-7777

..

Looksie ShipperID   CompanyName Phone
After   1           Speedy Express  (503) 555-9831
After   2           United Package  (503) 555-3199
After   3           Federal Shipping    (503) 555-9931
After   9           Shipper3C062D46-EEA (777) 555-7777

Answer 2

You can achieve this by changing SELECT * to SELECT 1. it might help

IF NOT EXISTS (SELECT 1 FROM Common.[CustomerxxxIds] 
               WHERE xyzType = @xyzType AND CustomerId = @CustomerId)

Answer 3

Try this

    ALTER PROCEDURE [Common].[SaveCustomerxxxIds] 
(
    @xyz NVARCHAR(128),
    @CustomerId INT,
    @xxxId   INT OUTPUT
)
AS
BEGIN
    set @xxxId=null
    --Get xxxId
    SELECT @xxxId=[xxxId] FROM Common.[CustomerxxxIds] WHERE xyz = @xyz AND CustomerId = @CustomerId
    --If @xxxId means no record we should insert 
    if (@xxxId is null)
        begin 
            --When insert we always insert xxxId as 0 then update to one then we collect the  value (one) from db and return it.
            --Better set value directly as one insert it to DB and return it as one. Instead of insert, update, select
            --just insert
            set @xxxId = 1
           INSERT INTO Common.[CustomerxxxIds]
                            ([xxxId]
                            ,[CustomerId]
                            ,[xyz])
                        VALUES
                            (@xxxId
                            ,@CustomerId
                            ,@xyz)
        end 
    else
        begin
            --If we have the value we add one to it update the record and return it.
            --better than update table then select. 
            --We already have the value we add one to it then update table and return the value we have 
            set @xxxId+=1
            UPDATE  Common.[CustomerxxxIds]   SET     [xxxId] = @xxxId
            WHERE   [xyz] = @xyz       AND CustomerId = @CustomerId
        END
end