2

To help me improve the performance of some queries I have created the following indexed view:

CREATE VIEW DBO.vwOrders
WITH SCHEMABINDING
AS 

/*-----------------------------------------------------------------------*\
   view for boccs2 reports
   29-sep-2015
\*-----------------------------------------------------------------------*/
SELECT 

strBxOrderNo,
sintMarketId,
sdtmOrdCreated,
strCurrencyCode,
sintOrderStatusID

FROM dbo.tblBOrder o 
WHERE 1=1
AND ( NOT o.sintOrderStatusId IN ( 9, 10, 11, 12, 13, 14 )
)
GO

I have created the clustered index for this view:

    set deadlock_priority high
    CREATE UNIQUE CLUSTERED INDEX PK_VWoRDERS_ 
ON DBO.vwOrders (sintMarketId,strBxOrderNo,sdtmOrdCreated)
    GO

the way I will mostly use the view ( amongst other things, including joint with other tables):

set transaction isolation level read uncommitted
set nocount on
set deadlock_priority high


--===============================================
-- set up the parameters
--===============================================

declare @market smallint 
declare @dateFrom smalldatetime
declare @dateto smalldatetime


select @dateFrom = '01-Sep-2015'
      ,@dateto   = '28-Sep-2015'
      ,@market   = 1


-- for testing the view
select * from DBO.vworders O
            where 1=1
            AND o.sdtmOrdCreated BETWEEN @dateFrom AND @dateto
            AND o.sIntMarketId = @market

this is the primary key on the base table - dbo.tblBOrder: strBxOrderNo varchar(20) not null

enter image description here

Based ONLY on these clauses above, how could I define my clustered index, and other indexes and why?

Moderators: I am aware this is not a strictly objective answer, but I consider the contents of the question valuable

here is an example of how I am currently using this view:

USE Bocss2

set transaction isolation level read uncommitted
set nocount on
set deadlock_priority high


--=================================================================
-- set up the parameters
--=================================================================

declare @market smallint 
declare @dateFrom smalldatetime
declare @dateto smalldatetime


select @dateFrom = '01-Sep-2015'
      ,@dateto   = '28-Sep-2015'
      ,@market   = 1


-- for testing the view
--select * from DBO.vworders O
--          where 1=1
--          AND o.sdtmOrdCreated BETWEEN @dateFrom AND @dateto
--          AND o.sIntMarketId = @market



--=================================================================
-- drop the tables if they already exist
--=================================================================

BEGIN TRY
  DROP TABLE #AllOrdersPaymentType 
END TRY
BEGIN CATCH
END CATCH

BEGIN TRY
  DROP TABLE #usGiftCards 
END TRY
BEGIN CATCH
END CATCH

--=================================================================
-- create the temp tables
--=================================================================
CREATE  TABLE #AllOrdersPaymentType   (

                                            strBxOrderNo VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED,
                                            sintMarketId smallint not null,
                                            strPaymentTypeDescr VARCHAR(50) NOT NULL,
                                            sdtmOrdCreated smalldatetime NOT NULL,
                                            strCurrencyCode varchar(3) not null,
                                            sintOrderStatusID smallint not null


                                       )

CREATE TABLE #usGiftCards (
                                           strItemNo varchar(8) NOT NULL PRIMARY KEY CLUSTERED,
                                           strTier3 varchar(20) not null,

                          )


--=================================================================
-- load the table #usGiftCards
--=================================================================
insert into #usGiftCards
SELECT uGC.strTier3
      ,uGC.strItemNo
FROM dbo.tblBGiftVoucherItem uGC
WHERE sintMarketID = 2
AND strType = 'CARD'
AND strTier1 LIKE 'GG%'

--=================================================================
-- load the table #usGiftCards
-- Add our payment type information
--=================================================================
            ;WITH R2 AS(

            select
            op.strBxOrderNo,

            RO=ROW_NUMBER() OVER (PARTITION BY op.strBxOrderNo ORDER BY (SELECT NULL)),
            intNunmPayMethods= COUNT (*) OVER (PARTITION BY op.strBxOrderNo),
            MINsintPaymentTypeID = MIN (op.sintPaymentTypeID ) OVER (PARTITION BY op.strBxOrderNo)


            ,OP.sintPaymentTypeID 
            ,optd.strPaymentTypeDescr
            ,o.sintMarketId

            ,sdtmOrdCreated
            ,o.strCurrencyCode
            ,o.sintOrderStatusID

            FROM
                dbo.tblBOrderPayment op --with (index(IX_tblBOrderPayment))
            inner join DBO.vworders O WITH (NOEXPAND)  on op.strBxOrderNo = o.strBxOrderNo

            inner join dbo.tblBOrderPaymentTypeDescr optd 
                    on op.sintPaymentTypeID = optd.sintPaymentTypeID 
                   and optd.sintLanguageID = 1

            where 1=1
            AND o.sdtmOrdCreated BETWEEN @dateFrom AND @dateto
            AND o.sIntMarketId = @market
            AND OP.decPaymentAmount > 0 --Exclude the zero payment record...(sometimes created by the system to track the CC details) 

            )
            ,R3 AS(

            SELECT 
             strBxOrderNo
             ,sintPaymentTypeID
            ,intNunmPayMethods
            ,strPaymentTypeDescr 
            ,sintMarketId
            ,sintOrderStatusID
            ,sdtmOrdCreated
            ,strCurrencyCode


            ,sintPaymentTypeID2 = MIN( CASE 
                                            WHEN intNunmPayMethods = 1 THEN sintPaymentTypeID 
                                            WHEN intNunmPayMethods > 1 AND sintPaymentTypeID IN (3,4,5) THEN 99
                                        ELSE sintPaymentTypeID
                                      end
                                     ) OVER (PARTITION BY strBxOrderNo)

            FROM R2
            where r2.ro = 1
           )

            INSERT 
            INTO #AllOrdersPaymentType
            SELECT 
             strBxOrderNo
             ,sintMarketId

            --,sintPaymentTypeID
            --,intNunmPayMethods
            --,strPaymentTypeDescr 
            --,sintPaymentTypeID2

            ,strPaymentTypeDescr= case intNunmPayMethods when 1 then strPaymentTypeDescr 
                                        else (
                                                case 
                                                    WHEN sintPaymentTypeID = 1 then 'Mixed (CC)'
                                                    when sintPaymentTypeID = 6 then 'Mixed (OI)'
                                                    when sintPaymentTypeID = 8 then 'Mixed (PayPal)'
                                                    when sintPaymentTypeID = 9 then 'Mixed (IDEAL)'
                                                    else 'Mixed (Other)' 
                                                end) 
                                 end
            ,sdtmOrdCreated
            ,strCurrencyCode
            ,sintOrderStatusID

             FROM R3

--select * from #AllOrdersPaymentType 
--=================================================================
-- Get redeemed vouchers from tblBOrderPaymentGiftVoucher
--=================================================================
SELECT m.strMarketDescrShort AS "Market",
CONVERT(VARCHAR(11), pt.sdtmOrdCreated, 103) AS "Order Date" ,
pt.strBxOrderNo AS "Bocss Ord No" ,
oSD.strOrderStatus AS "Order Status" ,
pt.strCurrencyCode AS "Currency" ,
strPaymentTypeDescr AS "Payment Type",
gVT.strVoucherId AS "Voucher ID" ,
gV.strIssuedBxOrderNo AS "Issued Ord No" ,
0 AS "GV Issued" ,
CASE WHEN gV.strIssuedBxOrderNo NOT LIKE 'P%' THEN gVV.lngValue ELSE 0 END AS "GV Redeemed" ,
CASE WHEN gV.strIssuedBxOrderNo LIKE 'P%' THEN gVV.lngValue ELSE 0 END AS "Promo GV Redeemed"
FROM  #AllOrdersPaymentType pt 
JOIN dbo.tblBMarket m ON pt.sintMarketID = m.sintMarketID
JOIN dbo.tblBOrderStatusDescr oSD ON pt.sintOrderStatusID = oSD.sintOrderStatusID
JOIN dbo.tblBOrderPayment oP ON pt.strBxOrderNo = oP.strBxOrderNo
LEFT JOIN dbo.tblBOrderPaymentGiftVoucher oPGV ON oP.lngPaymentID = oPGV.lngPaymentID
LEFT JOIN dbo.tblBGiftVoucher gV ON oPGV.strVoucherNumber = gV.strVoucherNumber
LEFT JOIN dbo.tblBGiftVoucherTranslation gVT ON gV.strVoucherNumber = gVT.strVoucherNumber
LEFT JOIN dbo.tblBGiftVoucherValue gVV ON gVT.strVoucherNumber = gVV.strVoucherNumber
WHERE 1=1
AND oSD.sintLanguageID = 1
AND oP.sintPaymentTypeID = 3

UNION 

--=================================================================
-- Get redeemed vouchers from tblBGiftVoucher
--=================================================================
SELECT m.strMarketDescrShort AS "Market",
CONVERT(VARCHAR(11), pt.sdtmOrdCreated, 103) AS "Order Date" ,
pt.strBxOrderNo AS "Bocss Ord No" ,
oSD.strOrderStatus AS "Order Status" ,
pt.strCurrencyCode AS "Currency" ,
strPaymentTypeDescr AS "Payment Type",
gVT.strVoucherId AS "Voucher ID" ,
gV.strIssuedBxOrderNo AS "Issued Ord No" ,
0 AS "GV Issued" ,
CASE WHEN gV.strIssuedBxOrderNo NOT LIKE 'P%' THEN gVV.lngValue ELSE 0 END AS "Non-Promo GV Redeemed" ,
CASE WHEN gV.strIssuedBxOrderNo LIKE 'P%' THEN gVV.lngValue ELSE 0 END AS "Promo GV Redeemed"
FROM dbo.tblBGiftVoucher gV
INNER JOIN #AllOrdersPaymentType pt  ON gV.strReedemedBxOrderNo = pt.strBxOrderNo
INNER JOIN dbo.tblBMarket m ON pt.sintMarketID = m.sintMarketID
INNER JOIN dbo.tblBOrderStatusDescr oSD ON pt.sintOrderStatusID = oSD.sintOrderStatusID
INNER JOIN dbo.tblBGiftVoucherTranslation gVT ON gV.strVoucherNumber = gVT.strVoucherNumber
INNER JOIN dbo.tblBGiftVoucherValue gVV ON gVT.strVoucherNumber = gVV.strVoucherNumber

UNION 
--=================================================================
-- Get issued vouchers from tblBGiftVoucher
--=================================================================
SELECT m.strMarketDescrShort AS "Market",
CONVERT(VARCHAR(11), pt.sdtmOrdCreated, 103) AS "Order Date" ,
pt.strBxOrderNo AS "Bocss Ord No" ,
oSD.strOrderStatus AS "Order Status" ,
pt.strCurrencyCode AS "Currency" ,
strPaymentTypeDescr AS "Payment Type",
gVT.strVoucherId AS "Voucher ID" ,
gV.strIssuedBxOrderNo AS "Issued Ord No" ,
gVV.lngValue AS "GV Issued" ,
0 AS "GV Redeemed" ,
0 AS "Promo GV Redeemed" 
FROM dbo.tblBGiftVoucher gV
INNER JOIN #AllOrdersPaymentType pt ON gV.strIssuedBxOrderNo = pt.strBxOrderNo
INNER JOIN dbo.tblBMarket m ON pt.sintMarketID = m.sintMarketID
INNER JOIN dbo.tblBOrderStatusDescr oSD ON pt.sintOrderStatusID = oSD.sintOrderStatusID
INNER JOIN dbo.tblBGiftVoucherTranslation gVT ON gV.strVoucherNumber = gVT.strVoucherNumber
INNER JOIN dbo.tblBGiftVoucherValue gVV ON gVT.strVoucherNumber = gVV.strVoucherNumber
WHERE 1=1
AND oSD.sintLanguageID = 1




UNION 

--=================================================================
-- Get issued vouchers for US by looking at order items
-- Does not return voucher numbers, returns seq number to make rows unique
--=================================================================
SELECT m.strMarketDescrShort AS "Market",
CONVERT(VARCHAR(11), pt.sdtmOrdCreated, 103) AS "Order Date" ,
pt.strBxOrderNo AS "Bocss Ord No" ,
oSD.strOrderStatus AS "Order Status" ,
pt.strCurrencyCode AS "Currency" ,
strPaymentTypeDescr AS "Payment Type",
CONVERT(VARCHAR, oI.sintOrderSeqNo) AS "Voucher ID" , -- Add sequence number to distinguish between multiple GV of the same value
pt.strBxOrderNo AS "Issued Ord No",
uGC.strTier3 AS "GV Issued" ,
0 AS "Non-Promo GV Redeemed" ,
0 AS "Promo GV Redeemed"
FROM #AllOrdersPaymentType pt
INNER JOIN dbo.tblBOrderItem oI ON pt.strBxOrderNo = oI.strBxOrderNo
INNER JOIN dbo.tblBMarket m ON pt.sintMarketID = m.sintMarketID
INNER JOIN dbo.tblBOrderStatusDescr oSD ON pt.sintOrderStatusID = oSD.sintOrderStatusID
INNER JOIN dbo.tblProdName pN ON oI.strItemNo = pN.strItemNo
INNER JOIN #usGiftCards uGC ON pN.strItemNo = uGC.strItemNo
WHERE oSD.sintLanguageID = 1
AND oI.sintOrderItemStatusId NOT IN (5, 9, 10)
ORDER BY pt.strBxOrderNo

Example of the output:

enter image description here

Execution plan when generating the temp table:

enter image description here

4

Based solely on this query:

select * 
from DBO.vworders O
where 1=1
and o.sdtmOrdCreated BETWEEN @dateFrom AND @dateto
and o.sIntMarketId = @market

The clustered index on the view should be:

-- Same columns, just in a query-friendly order
CREATE UNIQUE CLUSTERED INDEX PK_VWoRDERS_ 
ON DBO.vwOrders (sintMarketId,sdtmOrdCreated,strBxOrderNo);

Though to be certain of using the view (and to benefit from automatically creating statistics on the view), you should use the NOEXPAND hint, even in Enterprise Edition:

select * 
from DBO.vworders O WITH (NOEXPAND)
where 1=1
and o.sdtmOrdCreated BETWEEN @dateFrom AND @dateto
and o.sIntMarketId = @market

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.