I have the following update:
UPDATE dbo.fees SET amount = @amount, currencyCode = @currencyCode, feeType = @feeType, countryCode = @countryCode, gatewayCode = @gatewayCode, programAP = @programAP, programED = @programED, programEX = @programEX, mandatory = @mandatory WHERE feeID = @feeID and endDate = @endDate and programAP = @programAP and programED = @programED and programEX = @programEX and (countryCode = @countryCode or countryCode IS NULL) and (gatewayCode = @gatewayCode or gatewayCode IS NULL)
this is the table definition:
CREATE TABLE [dbo].[fees] ( [ID] INT IDENTITY(1,1) NOT NULL, [feeID] INT NOT NULL, [startDate] DATETIME NOT NULL, [endDate] DATETIME NULL, [amount] DECIMAL(10,2) NULL, [currencyCode] CHAR(3) NULL, [feeType] CHAR(1) NULL, [countryCode] CHAR(2) NULL, [gatewayCode] CHAR(3) NULL, [programAP] BIT NULL, [programEX] BIT NULL, [programED] BIT NULL, [mandatory] BIT NULL CONSTRAINT [DF_fees_mandatory] DEFAULT ((0)), CONSTRAINT [PK_fees] PRIMARY KEY CLUSTERED ([ID] asc) WITH FILLFACTOR = 90)
this is the index I intend to create in order to improve the performance of the update.
create nonclustered index i_feed2 on dbo.fees (feeID ,programAP,programED,programEX,endDate) include (countryCode,gatewayCode ) with(data_compression=page,online=on) on [NONCLUSTERED_INDEXES]
A question came to my mind though:
what sort of data analysis should I do, to identify which of these columns would be better in the index or in the
include part? Plus, the order of the columns in the index, is there any starting point as to find out, for this query in particular, or other queries that might hit this table, the best order of the columns?
There are no other indexes on this table.
I cannot change neither the table definition nor the update statement, but I can add indexes.
On this occasion though, even after adding the index as planned, while monitoring the execution and using the very helpful procedure sp_blitzcache
that I contacted the third party company requesting them to alter the procedure accordingly (
adding a with recompile and sort out the updates as described in the comments and the accepted answer)