sql-serverperformancecovering-index

The clarification of using covering indexes


As I know the covering index is index that should include all columns in SELECT statement. If so, what if I have query as following:

SELECT  ActionDate  -- Дата мероприятия
       ,ManagerName -- ФИО менеджера
       ,City        -- Город мероприятия
       ,Organazer   -- Юр.Лицо организатора
       ,[Action]    -- Мероприятие
       ,Category    -- Категория
       ,EnteringProfitability    -- Входящая доходность %
       ,PlaceId
       ,PlaceName
       -- Выручка
       ,SUM(isMetroCashDesk * Price) AS RevenueMetro    --'Выручка:Кассы-метро'
       ,SUM(isRestCashDesk * Price) AS RevenueRest   --'Выручка:Кассы-остальные'
       ,SUM(isPortal2CashDesk * Price) AS RevenuePortal2    --'Выручка:Портал 2.0'
       ,SUM(isEurosetCashDesk * Price) AS RevenueEuroset    --'Выручка:Евросеть'
       ,SUM(isPartnersCashDesk * Price) AS RevenuePartners  --'Выручка:Партнеры (субагенты)'
       ,SUM(isCashDesksPlatforms * Price) AS RevenueCashDesksPlatforms   --'Выручка:Кассы-площадки'
       -- Билеты
       ,SUM(isMetroCashDesk) AS TicketsMetro      --'Билеты:Кассы-метро'
       ,SUM(isRestCashDesk) AS TicketsRest  --'Билеты:Кассы-остальные'
       ,SUM(isPortal2CashDesk) AS TicketsPortal2     --'Билеты:Портал 2'
       ,SUM(isEurosetCashDesk) AS TicketsEuroset     --'Билеты:Евросеть'
       ,SUM(isPartnersCashDesk) AS TicketsPartners   --'Билеты:Партнеры (субагенты)'
       ,SUM(isCashDesksPlatforms) AS TicketsCashDesksPlatforms  --'Билеты:Кассы-площадки'
       -- Доход
       ,SUM(isMetroCashDesk * Income) AS IncomeMetro    --'Доход:Кассы-метро'
       ,SUM(isRestCashDesk * Income) AS IncomeRest   --'Доход:Кассы-остальные'
       ,SUM(isPortal2CashDesk * Income) AS IncomePortal2    --'Доход:Портал 2.0'
       ,SUM(isEurosetCashDesk * Income) AS IncomeEuroset    --'Доход:Евросеть'
       ,SUM(isPartnersCashDesk * Income) AS IncomePartners  --'Доход:Партнеры (субагенты)'
       -- Итого
       ,SUM(Price) AS RevenueTotal  --'Выручка:Итого, руб'
       ,COUNT(*) AS TicketsTotal    --'Билеты:Итого, шт'
       ,SUM(Income) AS IncomeTotal  --'Доход:Итого, руб'
       ,CASE SUM(Price) WHEN 0 THEN NULL ELSE 100.0 * SUM(Income)/SUM(Price) END AS Profitability   --'Доходность:Итого,%'
       -- Без касс-площадок и касс-устроителей
     ,SUM(CASE isCashDesksPlatforms WHEN 0 THEN Price ELSE 0 END) AS RevenueWithoutCashDesksPlatforms --Выручка
     ,SUM(CASE isCashDesksPlatforms WHEN 0 THEN 1 ELSE 0 END) AS TicketsWithoutCashDesksPlatforms --Билеты
     ,SUM(CASE isCashDesksPlatforms WHEN 0 THEN Income ELSE 0 END)  AS IncomeWithoutCashDesksPlatforms    --Доход
     ,CASE SUM(CASE isCashDesksPlatforms WHEN 0 THEN Price ELSE 0 END)
         WHEN 0 THEN NULL
         ELSE 100.0 * SUM(CASE isCashDesksPlatforms WHEN 0 THEN Income ELSE 0 END)/SUM(CASE isCashDesksPlatforms WHEN 0 THEN Price ELSE 0 END)
      END AS ProfitabilityWithoutCashDesksPlatforms --Доходность
FROM    #Goran
GROUP BY ActionDate
      ,ManagerName
      ,City
      ,Organazer
      ,[Action]
      ,Category
      ,EnteringProfitability
      ,PlaceId
      ,PlaceName
ORDER BY ActionDate
      ,ManagerName
      ,City
      ,Organazer
      ,[Action]
      ,Category

I think I cannot include all columns from SELECT part in covering index. If so, how can I improve the perfomance of this query?


Solution

  • Try this one -

    Index:

    CREATE NONCLUSTERED INDEX [IX_Goran] ON #Goran
    (
           ActionDate
         , ManagerName
         , City
         , Organazer
         , [Action]
         , Category
         , EnteringProfitability
         , PlaceName
    )
    INCLUDE (isMetroCashDesk, Price, ...)
    

    Query:

    SELECT   
           ActionDate
         , ManagerName 
         , City  
         , Organazer 
         , [Action]
         , Category 
         , EnteringProfitability
         , PlaceName
    
         , RevenueMetro = SUM(isMetroCashDesk * Price)
         , RevenueRest = SUM(isRestCashDesk * Price)
         , RevenuePortal2 = SUM(isPortal2CashDesk * Price)
         , RevenueEuroset = SUM(isEurosetCashDesk * Price)
         , RevenuePartners = SUM(isPartnersCashDesk * Price)
         , RevenueCashDesksPlatforms = SUM(isCashDesksPlatforms * Price)
    
         , TicketsMetro = SUM(isMetroCashDesk)
         , TicketsRest = SUM(isRestCashDesk)
         , TicketsPortal2 = SUM(isPortal2CashDesk)
         , TicketsEuroset = SUM(isEurosetCashDesk)
         , TicketsPartners = SUM(isPartnersCashDesk)
         , TicketsCashDesksPlatforms = SUM(isCashDesksPlatforms)
    
         , IncomeMetro = SUM(isMetroCashDesk * Income)
         , IncomeRest = SUM(isRestCashDesk * Income)
         , IncomePortal2 = SUM(isPortal2CashDesk * Income)
         , IncomeEuroset = SUM(isEurosetCashDesk * Income)
         , IncomePartners = SUM(isPartnersCashDesk * Income)
    
         , RevenueTotal = SUM(Price)
         , TicketsTotal = COUNT(1)
         , IncomeTotal = SUM(Income) 
         , Profitability = CASE WHEN SUM(Price) != 0 THEN 100.0 * SUM(Income) / SUM(Price) END
    
         , RevenueWithoutCashDesksPlatforms = ISNULL(SUM(CASE WHEN isCashDesksPlatforms = 0 THEN Price END), 0)
         , TicketsWithoutCashDesksPlatforms = ISNULL(SUM(CASE WHEN isCashDesksPlatforms = 0 THEN 1 END), 0)
         , IncomeWithoutCashDesksPlatforms = ISNULL(SUM(CASE WHEN isCashDesksPlatforms = 0 THEN Income END), 0)
         , ProfitabilityWithoutCashDesksPlatforms = 
              CASE WHEN SUM(CASE WHEN isCashDesksPlatforms = 0 THEN Price ELSE 0 END) != 0
                   THEN 100.0 * SUM(CASE WHEN isCashDesksPlatforms = 0 THEN Income ELSE 0 END) / SUM(CASE WHEN isCashDesksPlatforms = 0 THEN Price ELSE 0 END)
              END
    FROM #Goran
    GROUP BY 
           ActionDate
         , ManagerName
         , City
         , Organazer
         , [Action]
         , Category
         , EnteringProfitability
         , PlaceName
    ORDER BY 
           ActionDate
         , ManagerName
         , City
         , Organazer
         , [Action]
         , Category