I am trying to optimize a security computation process for a Web application by trying to making it synchronous (compute on the fly for an entity) instead of asynchronous (large computation for all entities once in a while).
I have broken down the business rules and the final step is to update the security information (INSERT, DELETE several dozens/hundreds records in a table that contains several million rows). The table definition is the following:
CREATE TABLE (dbo).(ComputedSecurityContract)(
(Id) (int) IDENTITY(1,1) NOT NULL,
(UserId) (int) NOT NULL,
(ContractId) (int) NOT NULL,
CONSTRAINT (PK_ComputedSecurityContract) PRIMARY KEY CLUSTERED
(
(Id) ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON (PRIMARY)
) ON (PRIMARY)
GO
My first trial involves a MERGE that seems to take more than 95% of the whole stuff the stored procedure is doing:
MERGE vComputedSecurityContract AS TARGET
USING #tmpContractSecurity AS SOURCE
ON (TARGET.UserId = SOURCE.UserId AND TARGET.ContractId = SOURCE.ContractId)
-- when matched there is nothing to be done
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET
THEN INSERT (ContractId, UserId) VALUES (SOURCE.ContractId, SOURCE.UserId)
--When there is a row that exists in target and same record does not exist in source then delete this record target
WHEN NOT MATCHED BY SOURCE
AND (@UserId IS NULL OR TARGET.UserId = @UserId)
AND (@ContractId IS NULL OR TARGET.ContractId = @ContractId)
THEN DELETE
OUTPUT $action,
DELETED.ContractId AS DeletedContractId, DELETED.UserId AS DeletedUserId,
INSERTED.ContractId AS InsertedContractId, INSERTED.UserId AS InsertedUserId;
Looking at the query plan, what struck me is the huge Excessive Grant (360MB, used some KB or 0 if no actual changes are to be made).
My second attempt is to break the MERGE into separate INSERT + DELETE statements and the relative cost of these queries is way less than that of the MERGE:
INSERT INTO vComputedSecurityContract (ContractId, UserId)
OUTPUT 'Inserted' AS Inserted, inserted.ContractId, inserted.UserId
SELECT T.ContractId, T.UserId
FROM #tmpContractSecurity T
WHERE NOT EXISTS (SELECT 1 FROM vComputedSecurityContract V where V.ContractId = T.ContractId AND V.UserId = T.UserId)
DELETE V
OUTPUT 'Deleted' AS Deleted, deleted.ContractId, deleted.UserId
FROM vComputedSecurityContract V
WHERE (@UserId IS NULL OR V.UserId = @UserId)
AND (@ContractId IS NULL OR V.ContractId = @ContractId)
AND NOT EXISTS (SELECT 1 FROM #tmpContractSecurity T where T.ContractId = V.ContractId and T.UserId = V.UserId)
I assume that the huge Excessive grant of the MERGE has something to do with this. Why these almost identical operations have such different performance results?
Note: @ContractId and @UserId are stored procedure input parameters.
