Skip to main content
added 764 characters in body
Source Link
pacreely
  • 1.1k
  • 1
  • 7
  • 15

I'd start with keeping the index to the columns that would assist the WHERE clause but not be included in the modified columns.

CREATE NONCLUSTERED INDEX idx ON  [dbo].[fees](feeID,endDate)

You don't have any VARCHARS so an UPDATE won't alter the CLUSTERED INDEX hierarchy and not require a modification to the NON Clustered Index.

The UPDATE statement is overwriting Values with the same value (this generates unnecessary overhead if the columns live in additional indexes). If you change your UPDATE statement to

UPDATE dbo.fees  
  SET amount = @amount,  
   currencyCode = @currencyCode,  
   feeType = @feeType, 
   countryCode = @countryCode,  
   gatewayCode = @gatewayCode, 
   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) 

Then you could introduce programAP, programED and programEX to the Index without incurring Index Update overhead.

Once you're happy with your UPDATE statement and you have a list of columns for your index, the first key column to include should be the one with the most varied number of values. Statistics for an index are only based on the first column of the index, and you want to help SQL narrow down to the smallest sub-set of rows possible. The rest is down to experimenting in your environment.

I'd start with keeping the index to the columns that would assist the WHERE clause but not be included in the modified columns.

CREATE NONCLUSTERED INDEX idx ON  [dbo].[fees](feeID,endDate)

You don't have any VARCHARS so an UPDATE won't alter the CLUSTERED INDEX hierarchy and not require a modification to the NON Clustered Index.

The UPDATE statement is overwriting Values with the same value (this generates unnecessary overhead if the columns live in additional indexes). If you change your UPDATE statement to

UPDATE dbo.fees  
  SET amount = @amount,  
   currencyCode = @currencyCode,  
   feeType = @feeType, 
   countryCode = @countryCode,  
   gatewayCode = @gatewayCode, 
   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) 

Then you could introduce programAP, programED and programEX to the Index without incurring Index Update overhead.

I'd start with keeping the index to the columns that would assist the WHERE clause but not be included in the modified columns.

CREATE NONCLUSTERED INDEX idx ON  [dbo].[fees](feeID,endDate)

You don't have any VARCHARS so an UPDATE won't alter the CLUSTERED INDEX hierarchy and not require a modification to the NON Clustered Index.

The UPDATE statement is overwriting Values with the same value (this generates unnecessary overhead if the columns live in additional indexes). If you change your UPDATE statement to

UPDATE dbo.fees  
  SET amount = @amount,  
   currencyCode = @currencyCode,  
   feeType = @feeType, 
   countryCode = @countryCode,  
   gatewayCode = @gatewayCode, 
   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) 

Then you could introduce programAP, programED and programEX to the Index without incurring Index Update overhead.

Once you're happy with your UPDATE statement and you have a list of columns for your index, the first key column to include should be the one with the most varied number of values. Statistics for an index are only based on the first column of the index, and you want to help SQL narrow down to the smallest sub-set of rows possible. The rest is down to experimenting in your environment.

added 764 characters in body
Source Link
pacreely
  • 1.1k
  • 1
  • 7
  • 15

I'd start with keeping the index to the columns that would assist the WHERE clause but not be included in the modified columns.

CREATE NONCLUSTERED INDEX idx ON  [dbo].[fees](feeID,endDate)

You don't have any VARCHARS so an UPDATE won't alter the CLUSTERED INDEX hierarchy and not require a modification to the NON Clustered Index.

The UPDATE statement is overwriting Values with the same value (this generates unnecessary overhead if the columns live in additional indexes). If you change your UPDATE statement to

UPDATE dbo.fees  
  SET amount = @amount,  
   currencyCode = @currencyCode,  
   feeType = @feeType, 
   countryCode = @countryCode,  
   gatewayCode = @gatewayCode, 
   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) 

Then you could introduce programAP, programED and programEX to the Index without incurring Index Update overhead.

I'd start with keeping the index to the columns that would assist the WHERE clause but not be included in the modified columns.

CREATE NONCLUSTERED INDEX idx ON  [dbo].[fees](feeID,endDate)

You don't have any VARCHARS so an UPDATE won't alter the CLUSTERED INDEX hierarchy and not require a modification to the NON Clustered Index.

I'd start with keeping the index to the columns that would assist the WHERE clause but not be included in the modified columns.

CREATE NONCLUSTERED INDEX idx ON  [dbo].[fees](feeID,endDate)

You don't have any VARCHARS so an UPDATE won't alter the CLUSTERED INDEX hierarchy and not require a modification to the NON Clustered Index.

The UPDATE statement is overwriting Values with the same value (this generates unnecessary overhead if the columns live in additional indexes). If you change your UPDATE statement to

UPDATE dbo.fees  
  SET amount = @amount,  
   currencyCode = @currencyCode,  
   feeType = @feeType, 
   countryCode = @countryCode,  
   gatewayCode = @gatewayCode, 
   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) 

Then you could introduce programAP, programED and programEX to the Index without incurring Index Update overhead.

Source Link
pacreely
  • 1.1k
  • 1
  • 7
  • 15

I'd start with keeping the index to the columns that would assist the WHERE clause but not be included in the modified columns.

CREATE NONCLUSTERED INDEX idx ON  [dbo].[fees](feeID,endDate)

You don't have any VARCHARS so an UPDATE won't alter the CLUSTERED INDEX hierarchy and not require a modification to the NON Clustered Index.