2

I have the following table:

IF OBJECT_ID('[dbo].[ShipTaxAddress]') IS NOT NULL 
DROP TABLE [dbo].[ShipTaxAddress] 
GO
CREATE TABLE [dbo].[ShipTaxAddress] ( 
[TaxRegionAddressId]  INT                              NOT NULL,
[TaxRegionId]         INT                              NOT NULL,
[CountryCode]         VARCHAR(2)                       NOT NULL,
[AddressFormatId]     INT                              NOT NULL,
[MatchAddressLine1]   NVARCHAR(50)                         NULL,
[MatchAddressLine2]   NVARCHAR(50)                         NULL,
[MatchAddressLine3]   NVARCHAR(50)                         NULL,
[MatchAddressLine4]   NVARCHAR(50)                         NULL,
[MatchAddressLine5]   NVARCHAR(50)                         NULL,
[MatchAddressLine6]   NVARCHAR(50)                         NULL,
[MatchPostalCode]     VARCHAR(20)                          NULL,
CONSTRAINT   [PK_ShipTaxAddress]  PRIMARY KEY CLUSTERED    ([TaxRegionAddressId] asc))

that has an identity as a clustered index.

however, as it is queried mostly according to these 2 columns:

[CountryCode]         VARCHAR(2)                       NOT NULL,
[AddressFormatId]     INT                              NOT NULL,

I thought about creating a clustered index on them.

they are pretty small, but they are not unique. should I include the id column in the cluster index so that I can make it unique?

Yes or No and Why?

This is the main query I would like to go faster:

ALTER PROCEDURE [dbo].[udpProductTaxRateGet]
(
    @itemNo varchar ( 20 ),
    @calculateDate datetime,
    @addressLine1 nvarchar( 50 ),
    @addressLine2 nvarchar( 50 ),
    @addressLine3 nvarchar( 50 ),
    @addressLine4 nvarchar( 50 ),
    @addressLine5 nvarchar( 50 ),
    @addressLine6 nvarchar( 50 ),
    @postalCode nvarchar( 20 ),
    @countryCode varchar( 2 ),
    @addressFormatID int
)
WITH EXECUTE AS 'webUserWithRW'
AS
--see Bocss2.dbo.[fnGetProductTax] for equivalent logic and comments in Bocss
DECLARE @Addresses TABLE (TaxRegionId int NOT NULL)

INSERT INTO @Addresses(TaxRegionId)
SELECT  DISTINCT TaxRegionId
FROM    dbo.[ShipTaxAddress]
WHERE   [CountryCode] = @countryCode
AND     [AddressFormatID] = @addressFormatID
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine1]), ISNULL(@addressLine1, '')) = ISNULL(@addressLine1, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine2]), ISNULL(@addressLine2, '')) = ISNULL(@addressLine2, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine3]), ISNULL(@addressLine3, '')) = ISNULL(@addressLine3, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine4]), ISNULL(@addressLine4, '')) = ISNULL(@addressLine4, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine5]), ISNULL(@addressLine5, '')) = ISNULL(@addressLine5, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine6]), ISNULL(@addressLine6, '')) = ISNULL(@addressLine6, '')
AND     @postalcode Like ISNULL ( CONVERT(nvarchar(20),[MatchPostalCode]), @postalcode)




SELECT DISTINCT ISNULL(pst.TaxCode, '') as TaxCode
     , ISNULL(pst.TaxRate, 0) as TaxRate
FROM    dbo.[ProductShipTax] pst
        INNER JOIN
        @Addresses a
            ON pst.TaxRegionId = a.TaxRegionId
WHERE   pst.[ItemNo] = @itemNo
AND     @calculateDate BETWEEN pst.[DateFrom] AND pst.[DateTo]

GO

This is how I improved this procedure:

I have created the following indexes:

    CREATE INDEX IDX_ShipTaxAddress_ShipTaxAddress
    ON dbo.[ShipTaxAddress] (CountryCode,
                             AddressFormatID,
                             MatchPostalCode)
    INCLUDE (TaxRegionId,
             [MatchAddressLine1],
             [MatchAddressLine2],
             [MatchAddressLine3],
             [MatchAddressLine4],
             [MatchAddressLine5],
             [MatchAddressLine6])
    GO

     CREATE NONCLUSTERED INDEX IX_ProductShipTax_ITemNo_DateFrom_DateTo 
     ON [dbo].[ProductShipTax] (  [ItemNo] ASC  
                                , [DateFrom] ASC  
                                , [DateTo] ASC  )   
    INCLUDE (TaxRegionId ,TaxCode,TaxRate)
    WITH (DROP_EXISTING=ON)

I have changed the relevant columns in the table, from VARCHAR to NVARCHAR, in order to eliminate the need of conversion. the Table became like this:

IF OBJECT_ID('[dbo].[ShipTaxAddress]') IS NOT NULL 
DROP TABLE [dbo].[ShipTaxAddress] 
GO
CREATE TABLE [dbo].[ShipTaxAddress] ( 
[TaxRegionAddressId]  INT                              NOT NULL,
[TaxRegionId]         INT                              NOT NULL,
[CountryCode]         VARCHAR(2)                       NOT NULL,
[AddressFormatId]     INT                              NOT NULL,
[MatchAddressLine1]   NVARCHAR(50)                         NULL,
[MatchAddressLine2]   NVARCHAR(50)                         NULL,
[MatchAddressLine3]   NVARCHAR(50)                         NULL,
[MatchAddressLine4]   NVARCHAR(50)                         NULL,
[MatchAddressLine5]   NVARCHAR(50)                         NULL,
[MatchAddressLine6]   NVARCHAR(50)                         NULL,
[MatchPostalCode]     NVARCHAR(20)                         NULL,
CONSTRAINT   [PK_ShipTaxAddress]  
PRIMARY KEY NONCLUSTERED ([TaxRegionAddressId] asc))
GO

I have changed the procedure:

ALTER PROCEDURE [DenormV2].[udpProductTaxRateGet]
(
    @itemNo varchar ( 20 ),
    @calculateDate datetime,
    @addressLine1 nvarchar( 50 ),
    @addressLine2 nvarchar( 50 ),
    @addressLine3 nvarchar( 50 ),
    @addressLine4 nvarchar( 50 ),
    @addressLine5 nvarchar( 50 ),
    @addressLine6 nvarchar( 50 ),
    @postalCode nvarchar( 20 ),
    @countryCode varchar( 2 ),
    @addressFormatID int
)
WITH EXECUTE AS 'webUserWithRW'
AS
--see Bocss2.dbo.[fnGetProductTax] for equivalent logic and comments in Bocss


SELECT @postalcode    = CASE WHEN @postalcode   = N'' THEN NULL ELSE @postalcode   END
SELECT @addressLine1  = CASE WHEN @addressLine1 = N'' THEN NULL ELSE @addressLine1 END
SELECT @addressLine2  = CASE WHEN @addressLine2 = N'' THEN NULL ELSE @addressLine2 END
SELECT @addressLine3  = CASE WHEN @addressLine3 = N'' THEN NULL ELSE @addressLine3 END
SELECT @addressLine4  = CASE WHEN @addressLine4 = N'' THEN NULL ELSE @addressLine4 END
SELECT @addressLine5  = CASE WHEN @addressLine5 = N'' THEN NULL ELSE @addressLine5 END
SELECT @addressLine6  = CASE WHEN @addressLine6 = N'' THEN NULL ELSE @addressLine6 END



SELECT TOP 1  ISNULL(pst.TaxCode, '') as TaxCode
            , ISNULL(pst.TaxRate, 0) as TaxRate
FROM    dbo.[ProductShipTax] pst
WHERE EXISTS (

    SELECT  TaxRegionId
    FROM    dbo.[ShipTaxAddress]
    WHERE   [CountryCode] = @countryCode
    AND     [AddressFormatID] = @addressFormatID

    AND ([MatchAddressLine1] = @AddressLine1 OR ([MatchAddressLine1] IS NULL AND @AddressLine1 IS NULL) )
    AND ([MatchAddressLine2] = @AddressLine2 OR ([MatchAddressLine2] IS NULL AND @AddressLine2 IS NULL) )
    AND ([MatchAddressLine3] = @AddressLine3 OR ([MatchAddressLine3] IS NULL AND @AddressLine3 IS NULL) )
    AND ([MatchAddressLine4] = @AddressLine4 OR ([MatchAddressLine4] IS NULL AND @AddressLine4 IS NULL) )
    AND ([MatchAddressLine5] = @AddressLine5 OR ([MatchAddressLine5] IS NULL AND @AddressLine5 IS NULL) )
    AND ([MatchAddressLine6] = @AddressLine6 OR ([MatchAddressLine6] IS NULL AND @AddressLine6 IS NULL) )

    AND (@postalcode = [MatchPostalCode]     OR ([MatchPostalCode]   IS NULL AND @postalcode   IS NULL) )
    AND  TaxRegionId = pst.TaxRegionId     

)
AND pst.[ItemNo] = @itemNo
AND @calculateDate BETWEEN pst.[DateFrom] AND pst.[DateTo]

GO

when comparing the following:

USE US16HSMMProduct_ORIGINAL
GO
exec dbo.udpProductTaxRateGet 
        @itemNo=N'31997299',
        @calculateDate='Aug  8 2016  1:01:46:760PM',
        @addressLine1=N'',
        @addressLine2=N'',
        @addressLine3=N'',
        @addressLine4=N'',
        @addressLine5=N'',
        @addressLine6=N'FL',
        @postalcode=N'',
        @countryCode=N'US',
        @addressFormatID=2
go
USE US16HSMMProduct_AFTER_CHANGES
GO
exec DenormV2.udpProductTaxRateGet
        @itemNo=N'31997299',
        @calculateDate='Aug  8 2016  1:01:46:760PM',
        @addressLine1=N'',
        @addressLine2=N'',
        @addressLine3=N'',
        @addressLine4=N'',
        @addressLine5=N'',
        @addressLine6=N'FL',
        @postalcode=N'',
        @countryCode=N'US',
        @addressFormatID=2
go

we get this: enter image description here

here is the execution plan of the old procedure

here is the execution plan of the new procedure

  • 1
    Id say test it out in your environment as a unique index can help the engine make much better choices. Test create the unique one and run your queries against it after dumping your plan cache, then compare plans. You might notice much better joins and faster results from the unique one. Do note the constraint will need to be unique checked every write which will have a very tiny impact on writes, but if you're doing thousands of write operations a second.... – Ali Razeghi Aug 9 '16 at 16:32
  • Why all the convert to nvarchar(50) ? – paparazzo Aug 9 '16 at 17:09
  • @Paparazzi I am not sure about that, on the table these fields are stored as VARCHAR. I might have to alter the columns in the table from varchar to nvarchar, since that seems easier than changing the application – Marcello Miorelli Aug 9 '16 at 17:13
  • 1
    But the posted table definition is nvarchar. Have the @ variables match the actual column type. – paparazzo Aug 9 '16 at 17:18
  • Why are you using a @Addresses table variable? – paparazzo Aug 9 '16 at 17:24
2

If you were to use those two columns as the clustered index, first have to change the PK to non-clustered as BlackStar mentioned, and they are not unique, then SQL Server will add a uniquifier to make it unique. That uniquifier is 4 bytes and used only when necessary. You could instead add the identity column to the end of that index. The identity column is defined as int which is 4 bytes, so there wouldn't be a size difference between the two versions of the index.

EDIT now that the main query has been provided.

I would remove the table variable if you want it to go faster. Do it all in one query. You can easily use a CTE instead.

Like this:

WITH a
AS
(
SELECT  DISTINCT TaxRegionId
FROM    dbo.[ShipTaxAddress]
WHERE   [CountryCode] = @countryCode
AND     [AddressFormatID] = @addressFormatID
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine1]), ISNULL(@addressLine1, '')) = ISNULL(@addressLine1, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine2]), ISNULL(@addressLine2, '')) = ISNULL(@addressLine2, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine3]), ISNULL(@addressLine3, '')) = ISNULL(@addressLine3, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine4]), ISNULL(@addressLine4, '')) = ISNULL(@addressLine4, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine5]), ISNULL(@addressLine5, '')) = ISNULL(@addressLine5, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine6]), ISNULL(@addressLine6, '')) = ISNULL(@addressLine6, '')
AND     @postalcode Like ISNULL ( CONVERT(nvarchar(20),[MatchPostalCode]), @postalcode))

SELECT DISTINCT ISNULL(pst.TaxCode, '') as TaxCode
     , ISNULL(pst.TaxRate, 0) as TaxRate
FROM    dbo.[ProductShipTax] pst
        INNER JOIN
        a
            ON pst.TaxRegionId = a.TaxRegionId
WHERE   pst.[ItemNo] = @itemNo
AND     @calculateDate BETWEEN pst.[DateFrom] AND pst.[DateTo]
  • 1
    I think the uniquifier is variable length so it adds two more bytes per row. Have a look at chat.stackexchange.com/transcript/179/2013/8/15/6-7 and follow the link to the video for more info. – Mikael Eriksson Aug 9 '16 at 16:24
  • 2
    @MikaelEriksson, that's interesting as I'd never come across that it was variable. I guess the size is 4 bytes, but that there is also overhead. Found this as I was too lazy to watch video: aboutsqlserver.com/2011/11/24/…. It's tl;dr, so answer is at the bottom. – Tara Kizer Aug 9 '16 at 16:36
  • @MikaelEriksson from what I've read it's variable in the sense that it's 4 or 0 depending on uniqueness. However that does mean that if your CI is mostly unique it will definitely be smaller than adding the int. (most cases won't need the uniquifier so it will be smaller) – Kenneth Fisher Aug 9 '16 at 17:29
1

Actually, the primary reason for making indexes is for speeding up query's. So if you are trying to speed up your query then, as long as the index have the necessary and only necessary columns the best the index would be.

However, you already have a CLUSTERED PRIMARY KEY that insides resides a CLUSTERED INDEX so you can't have both at the CLUSTERED INDEX at the same time.

Rather you choose to make your PRIMARY KEY not CLUSTERED and then your INDEX could be CLUSTERED. Otherwise, you won't be able to make another CLUSTERED INDEX. You have to evaluate what is the most important and cost time and performance.

Also, you INDEXES are not made to be UNIQUE by purpose, if you strongly believe you usually search by [CountryCode] and [AddressFormatId] more often than any other column, then the advise would be to create the PRIMARY KEY as NOT CLUSTERED and create the CLUSTERED INDEX containing just the pair of two columns that you use for searching and joining.

NOTE: the CLUSTERED INDEX would work best just by looking up for both columns at the time, if you believe those columns are the most searchable ones but not both at the same time, then the INDEX would drawback the performance.

-3

I would leave the PK clustered index alone

create two non clustered indexes on

[CountryCode]
[AddressFormatId]

as for query that was added late

there is no ItemNo in the table definition

since you are joining on TaxRegionId

index on TaxRegionId

SELECT DISTINCT ISNULL(pst.TaxCode, '') as TaxCode
     , ISNULL(pst.TaxRate, 0) as TaxRate
FROM    dbo.[ProductShipTax] pst
INNER JOIN dbo.[ShipTaxAddress] a 
      ON pst.TaxRegionId = a.TaxRegionId
WHERE   [CountryCode] = @countryCode
AND     [AddressFormatID] = @addressFormatID
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine1]), ISNULL(@addressLine1, '')) = ISNULL(@addressLine1, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine2]), ISNULL(@addressLine2, '')) = ISNULL(@addressLine2, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine3]), ISNULL(@addressLine3, '')) = ISNULL(@addressLine3, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine4]), ISNULL(@addressLine4, '')) = ISNULL(@addressLine4, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine5]), ISNULL(@addressLine5, '')) = ISNULL(@addressLine5, '')
AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine6]), ISNULL(@addressLine6, '')) = ISNULL(@addressLine6, '')
AND     @postalcode Like ISNULL ( CONVERT(nvarchar(20),[MatchPostalCode]), @postalcode)

and     pst.[ItemNo] = @itemNo
AND     @calculateDate BETWEEN pst.[DateFrom] AND pst.[DateTo]  
  • I have added the main query I am tuning, related to this table. – Marcello Miorelli Aug 9 '16 at 17:00
  • 1
    I would start with the two in answer and test – paparazzo Aug 9 '16 at 17:04
  • 1
    @Paparazzi, if both columns are used in most queries, then it wouldn't make sense to have two indexes with one column each. A composite index would perform better, meaning both columns are in one index. I would test this composite index as non-clustered and then test again as clustered. The test should include several different queries. – Tara Kizer Aug 9 '16 at 18:16
  • @Tara "as it is queried mostly according to these 2 columns" If you take that as the query is always both columns then yes. – paparazzo Aug 9 '16 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.