varchar and nvarchar in tuning a stored procedure - how to improve performance in this scenario?

I have the following procedure that is called over a million times a day, and I think it can be tuned for better resources usage.

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
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 procedure inserts values in

• get rid of the conversions to nvarchar. Your tables are using varchar, change your parameters to be varchar also.

-
Get rid of the logic like this:

AND     ISNULL (CONVERT(nvarchar(50),[MatchAddressLine1]), ISNULL(@addressLine1, '')) = ISNULL(@addressLine1, '')

SQL doesn't work as well when you use functions on the columns in the where clause. Instead do this (and remember we are getting rid of the nvarchar parameters.

AND ([MatchAddressLine1] = @AddressLine1
     OR ([MatchAddressLine1] IS NULL and @AddressLine1 IS NULL) )

Note your current logic would return a row where one is NULL and the other is ''. If you still need that logic you'll have to add two more OR options but it will still work. The optimizer can work much better with this type of logic.

-
Also you might change from a table variable to a temp table. There is a significant difference that you can see here.

-
Last option is you could get rid of the temp table/variable entirly and use a CTE.

WITH Addresses AS (
    SELECT  DISTINCT 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 IS NULL OR [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]

You should check my code and make sure the logic is going to be correct but I believe it will be. Also use something like SET STATISTICS IO ON to get a good time (in milliseconds) of how long it runs before and after. Stranger things have happened than your code would be faster than mine.

StackExchange Database Administrators Q#146290, answer score: 5