Sql-server – what would be a good choice of clustered index (and other indexes) for this situation involving a indexed view

indexmaterialized-viewperformancequery-performancesql serversql server 2014

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

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:

Execution plan when generating the temp table:

Best Answer

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

SUGGESTION #1 : Use index with different column order

ALTER TABLE sales_reps ADD UNIQUE INDEX
(state,account_id,name,platform,client_group,from_date);

Note that state and account_id are reversed. That way, there is no need to traverse all rows for a given account_id that would separate enabled from disabled. Only enabled entries for account_id are read.

SUGGESTION #2 : Refactor the Query

Instead of

SELECT * 
FROM `sales_reps` 
WHERE `sales_reps`.`account_id` = 1 
AND state = 'enabled' 
AND (clients >= 1 OR revenue >= 100) 
ORDER BY name,platform,client_group,from_date

Try this experiment

First run this query

SELECT enabled,accountid,name,platform,client_group,from_date
FROM sales_rep WHERE account_id=1 AND state='enabled'
ORDER BY enabled,accountid,name,platform,client_group,from_date

then run this query

SELECT * FROM sales_rep WHERE account_id=1 AND state='enabled'
AND clients >= 1 OR revenue >= 100

If the performance of those two queries are fast, then JOIN them as subqueries and see

SELECT B.* FROM
(
    SELECT enabled,accountid,name,platform,client_group,from_date
    FROM sales_rep WHERE account_id=1 AND state='enabled'
    ORDER BY enabled,accountid,name,platform,client_group,from_date
) A LEFT JOIN
(
    SELECT * FROM sales_rep WHERE account_id=1 AND state='enabled'
    AND clients >= 1 OR revenue >= 100
) B USING (enabled,accountid,name,platform,client_group,from_date)
WHERE B.enabled IS NOT NULL;

Give it a Try !!!

Sql-server – What factors go into an Indexed View’s Clustered Index being selected

Matching indexed views is a relatively expensive operation*, so the optimizer tries other quick and easy transformations first. If those happen to produce a cheap plan (0.05 units in your case) optimization ends early. The bet is that continued optimization would consume more time than it saved. Remember the optimizer's primary goal is a 'good enough' plan quickly.

Using the clustered index on the view isn't expensive in itself, but the process of matching a logical query tree to potential indexed views can be. As I mentioned in a comment on the other question, the view reference in the query is expanded before optimization, so the optimizer doesn't know you wrote the query against the view in the first place - it sees only the expanded tree (as if the view had been in-lined).

"Good Enough Plan" means the optimizer found a decent plan and stopped early in an exploration phase. "TimeOut" means it exceeded the number of optimization steps it set itself as a 'budget' at the start of the current phase.

The budget is set based on the cost of the best plan found in a previous phase. With such a low-cost query (0.05) the number of budgeted moves will be quite small, and quickly exhausted by regular transformation given the number of joins involved in your sample query (there are lots of ways to rearrange inner joins, for example).

If you are interested to know more about why indexed view matching is expensive, and therefore left for later stages of optimization and/or only considered for more costly queries, there are two Microsoft Research Papers on the topic here (pdf) and here (citeseer).

Another relevant factor is that indexed view matching is not available in optimization phase 0 (transaction processing).

Best Answer

Related Solutions

Mysql – What would be a optimal index for this query in thesql

SUGGESTION #1 : Use index with different column order

SUGGESTION #2 : Refactor the Query

Sql-server – What factors go into an Indexed View’s Clustered Index being selected

Related Question