2

This query

with periods (PeriodStart,PeriodEnd,ticker,FundamentalId,dimension) as
  (select b.reportdate PeriodStart, MIN(a.reportdate) PeriodEnd,b.ticker, b.FundamentalId,b.dimension
  from Base a
  left join Base b on a.ticker = b.ticker AND a.dimension = b.dimension
  where a.reportdate > b.reportdate
  group by b.reportdate,b.ticker,b.FundamentalId, b.dimension),

  lastPeriods (PeriodStart,PeriodEnd,ticker,FundamentalId,dimension) as
  (select a.PeriodStart,GETDATE() PeriodEnd, a.ticker, b.FundamentalId,a.dimension 
from
(select MAX(reportdate) PeriodStart, ticker, dimension from Base group by ticker,dimension) a
left join Base b on b.reportdate = a.PeriodStart AND b.ticker = a.ticker AND b.dimension = a.dimension)

select 
AVG(Base.dividend) OVER(PARTITION BY Base.ticker, Base.dimension) as divAverage, 
Prices.ticker,Prices.closeDate,Base.dimension, allPeriods.FundamentalId
 from
(
(select * from periods)
UNION
(select * from lastPeriods)
) allPeriods
left join Prices ON Prices.ticker = allPeriods.ticker and (allPeriods.PeriodStart<= Prices.closedate AND Prices.closedate< allPeriods.PeriodEnd)
left join Base ON allPeriods.FundamentalId = Base.FundamentalId
where closedate>='2019-06-26' AND Prices.ticker = 'ABC' AND Base.dimension='ARQ';

Uses my indexes and runs fast. However when I create a view from this query

create view pricing as
with periods (PeriodStart,PeriodEnd,ticker,FundamentalId,dimension) as
  (select b.reportdate PeriodStart, MIN(a.reportdate) PeriodEnd,b.ticker, b.FundamentalId,b.dimension
  from Base a
  left join Base b on a.ticker = b.ticker AND a.dimension = b.dimension
  where a.reportdate > b.reportdate
  group by b.reportdate,b.ticker,b.FundamentalId, b.dimension),

  lastPeriods (PeriodStart,PeriodEnd,ticker,FundamentalId,dimension) as
  (select a.PeriodStart,GETDATE() PeriodEnd, a.ticker, b.FundamentalId,a.dimension 
from
(select MAX(reportdate) PeriodStart, ticker, dimension from Base group by ticker,dimension) a
left join Base b on b.reportdate = a.PeriodStart AND b.ticker = a.ticker AND b.dimension = a.dimension)

select 
AVG(Base.dividend) OVER(PARTITION BY Base.ticker, Base.dimension) as divAverage, 
Prices.ticker,Prices.closeDate,Base.dimension, allPeriods.FundamentalId
 from
(
(select * from periods)
UNION
(select * from lastPeriods)
) allPeriods
left join Prices ON Prices.ticker = allPeriods.ticker and (allPeriods.PeriodStart<= Prices.closedate AND Prices.closedate< allPeriods.PeriodEnd)
left join Base ON allPeriods.FundamentalId = Base.FundamentalId

and query the view like

 select * from pricing where closedate>='2019-06-26' AND ticker = 'ABC' AND dimension='ARQ';

It runs slow and doesnt use my indexes, it does index scans and a filter at the end. Also, if i take out the AVG(Base.dividend) OVER(PARTITION BY Base.ticker, Base.dimension) as divAverage it runs fast in both cases. But, I need the average.

How can I get it to use my indexes?

Here are the actual tables

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Base](
    [FundamentalId] [int] IDENTITY(1,1) NOT NULL,
    [ticker] [nvarchar](50) NULL,
    [reportdate] [datetime] NOT NULL,
    [dividend] [decimal](18, 2) NOT NULL,
    [dimension] [nvarchar](50) NULL,
 CONSTRAINT [PK_dbo.Base] PRIMARY KEY CLUSTERED 
(
    [FundamentalId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prices](
    [PriceId] [int] IDENTITY(1,1) NOT NULL,
    [ticker] [nvarchar](50) NULL,
    [closedate] [datetime] NOT NULL,
    [price] [decimal](20, 4) NULL,
 CONSTRAINT [PK_dbo.Prices] PRIMARY KEY CLUSTERED 
(
    [PriceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ticker_closedate] ON [dbo].[Prices]
(
    [ticker] ASC,
    [closedate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ticker_dimension_datekey] ON [dbo].[Fundamentals]
(
    [ticker] ASC,
    [dimension] ASC,
    [datekey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
  • post both the plan using brentz paste the plan may be that can lead to something – Biju jose Jul 7 at 8:36
  • Someone had posted a link to another thread that described the problem, but then removed the comment. I will see if I can find it again. I could not get it to work in a view and had to use an inline function. – jram1983 Jul 9 at 1:39

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.