2

I got a question regarding the best way to put a bad column for an index in an index, so that I can satisfy my query below, and similar others that follow the same pattern.

on the query below, all the columns in the select come from table TableBackups.[dbo].[tblBOrderItem].

I only go to the other table - TableBackups.[dbo].[tblBOrder] - to get the orders that are between the @fromDate and @todate AND the order must have a value which in my system here it translates to

i.decCatItemPrice > 0

SET STATISTICS IO ON 

DECLARE @FromDate DATETIME = '27 feb 2016'
DECLARE @ToDate DATETIME = '28 feb 2016'

    SELECT  i.[strBxOrderNo] ,
            [sintOrderSeqNo] ,
            [sintOrderItemStatusId] ,
            [decBCTaxAmount] ,
            [decBCFinalPrice] ,
            [decBCOverrideDiscount] ,
            [decBCOfferDiscount] ,
            [decBCCatItemPrice] ,
            [decBCCostPrice] ,
            [decTaxAmount] ,
            [decFinalPrice] ,
            [decOverrideDiscount] ,
            [decOfferDiscount] ,
            [decCatItemPrice] ,
            [decCostPrice] ,
            [decWasPrice] ,
            [dtmOrdItemCreated] ,
            [sintOrderItemType] ,
            [strItemNo] ,
            [sintQuantity] ,
            [blnChargeShipping] ,
            i.[strBxOrderNo] + '_' + 
              CONVERT(VARCHAR(5), i.[sintOrderSeqNo]) AS OrderNoItemSeqNo
    FROM    TableBackups.[dbo].[tblBOrderItem] i


    where exists ( select * from TableBackups.[dbo].[tblBOrder] o
                   where o.[sdtmOrdCreated] >= @FromDate
                    AND o.[sdtmOrdCreated] < @ToDate
                    AND i.[strBxOrderNo] = o.[strBxOrderNo]
                    AND i.decCatItemPrice > 0
                 ) 

the primary key of table TableBackups.[dbo].[tblBOrder] is:

 ALTER TABLE [dbo].[tblBorder] 
 ADD  CONSTRAINT [PK_tblBOrder] 
 PRIMARY KEY CLUSTERED (  [strBxOrderNo] ASC  )  
  WITH (  PAD_INDEX = OFF ,
          FILLFACTOR = 95  ,
          SORT_IN_TEMPDB = OFF , 
          IGNORE_DUP_KEY = OFF , 
          STATISTICS_NORECOMPUTE = OFF , 
          ONLINE = OFF , 
          ALLOW_ROW_LOCKS = ON , 
          ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 

the primary key of table TableBackups.[dbo].[tblBOrderItem] is:

 ALTER TABLE [dbo].[tblBorderItem] 
 ADD  CONSTRAINT [PK_tblBOrderItem] 
 PRIMARY KEY CLUSTERED (  [strBxOrderNo] ASC  , 
                          [sintOrderSeqNo] ASC  )   
WITH (  PAD_INDEX = OFF ,
        FILLFACTOR = 95  ,
        SORT_IN_TEMPDB = OFF , 
        IGNORE_DUP_KEY = OFF , 
        STATISTICS_NORECOMPUTE = OFF , 
        ONLINE = OFF , 
        ALLOW_ROW_LOCKS = ON , 
        ALLOW_PAGE_LOCKS = ON  ) 
        ON [PRIMARY ] 

when I run the following command to get the data types in table tblBorderItem:

sp_help 'tblBorderItem'

enter image description here

there are no foreign keys connection the item tables to the order table. although the column strBxOrderNo varchar(20) is on the primary key of both tables.

sp_help 'tblBorder'

enter image description here

this is the index I have created to improve the performance of this query:

    -- THE NEW INDEX - FILTERED
      CREATE NONCLUSTERED INDEX 
IX_tblBOrderItem_decCatItemPrice_INCL 
ON [dbo].[tblBorderItem] (  [decCatItemPrice] , 
                            [strBxOrderNo] , 
                            [strItemNo] )   
     INCLUDE ( [blnChargeShipping] , 
     [decBCCatItemPrice] , 
     [decBCCostPrice] , 
     [decBCFinalPrice] , 
     [decBCOfferDiscount] , 
     [decBCOverrideDiscount] , 
     [decBCTaxAmount] , 
     [decCostPrice] , 
     [decFinalPrice] , 
     [decOfferDiscount] , 
     [decOverrideDiscount] , 
     [decTaxAmount] , 
     [decWasPrice] , 
     [dtmOrdItemCreated] , 
     [sintOrderItemStatusId] , 
     [sintOrderItemType] , 
     [sintOrderSeqNo] , 
     [sintQuantity] )
     WHERE [decCatItemPrice]  > 0
     WITH (  PAD_INDEX = OFF ,
             FILLFACTOR = 95 ,
             SORT_IN_TEMPDB = OFF , 
             IGNORE_DUP_KEY = OFF ,
             STATISTICS_NORECOMPUTE = OFF , 
             ONLINE = OFF , 
             DROP_EXISTING = ON , 
             ALLOW_ROW_LOCKS = ON , 
             ALLOW_PAGE_LOCKS = ON  )

the questions:

1) [decCatItemPrice] is better off as an index column or as a column in the INCLUDE? how do you decide?

I have taken my decisions through try and error, and this index arrangement is the one that reduced the logical reads number to the lowest as you can see on the picture below.

enter image description here

2) I also have the reverse query (all columns come from the order table) but I still need to get ONLY the orders which have i.decCatItemPrice > 0 would the same index work for both?

  • 3
    Why is the [strItemNo] in the index? It's not used in the query. And have you tried a simple index on (decCatItemPrice)? It's equivalent I think to the composite (decCatItemPrice, strBxOrderNo) in this case, due to the choice of the CI. – ypercubeᵀᴹ Mar 14 '16 at 18:03
  • 3
    But really, much depends on distribution. What percent of the table rows have a positive value in that decCatItemPrice column? If it's not a very small percentage, then a full table scan may be unavoidable (and better)! – ypercubeᵀᴹ Mar 14 '16 at 18:08
  • 3
    If the percentage is very small (or in other words, if the condition decCatItemPrice > 0 does reduce the amount of returned rows by a large factor) then a simpler and smaller index on (PK columns) WHERE (decCatItemPrice > 0) might be better than the much wider index you have made. – ypercubeᵀᴹ Mar 14 '16 at 18:17

Your Answer

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

Browse other questions tagged or ask your own question.