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 and 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.