0

I have read in forums that index views can speed up the performance since it stores the table. How can I modify my below query to use NewDate as a unique index? I also know there are some limitations with respect to index queries. Can someone guide me here?

CREATE VIEW [dbo].[DenisGroup] AS
SELECT NewDate,
TStamp,EnergyAC3,Irradiation,EnergyAC2,StationID,EnergyAC,
FROM
(
ROW_NUMBER() OVER (PARTITION BY CAST(Date1 AS date) ORDER BY TStamp DESC) AS Seq,TRY_CAST(Date1 as date) AS NewDate, Tstamp, 
case when ([Eac1] >6000 OR [Eac1] = NULL OR [Eac1]=0)
    THEN '5000'
    ELSE [Eac1]
       end AS EnergyAC3,
       (([DailySpecYield1]*100)/[PR1]) AS Irradiation, [LastTime] , 0 AS EnergyAC2,REPLACE(StnId, 'MY-001X','MY-001') as StationID,0 AS EnergyAC
FROM [dbo].[MY-001X-4G]
UNION ALL
ROW_NUMBER() OVER (PARTITION BY CAST(Date1 AS date) ORDER BY TStamp DESC) AS Seq,TRY_CAST(Date1 as date) AS NewDate, Tstamp, 
case when ([MFM1.Eac2] >6000 OR [MFM1.Eac2] = NULL OR [MFM1.Eac2]=0)
    THEN '5000'
    ELSE [MFM1.Eac2]
       end AS EnergyAC,
       case when ([WMS.GTI] IS NULL)
       then '4.52'
       when ([WMS.GTI] < 0) then '3'
       when ([WMS.GTI]> 7) then '3.95'
       else [WMS.GTI] end AS Irradiation, [MFM1.LastTime] , [MFM2-Eac1] AS EnergyAC2,0 AS EnergyAC3,REPLACE(StnId, 'MY-002C','MY-002') as StationID
FROM [dbo].[MY-002C-4G]
)t
WHERE Seq = 1
GO
raghav
  • 45
  • 1
  • 1
  • 9
  • If you have a performance issue, _don't_ start with indexed views. There is most likely a far better solution to your issue. Indexed views are only useful in very limited and very specific circumstances – Nick.Mc Dec 04 '18 at 03:57
  • @Nick.McDermaid What is the alternate method? – raghav Dec 04 '18 at 04:08
  • That depends entirely on what your _actual problem_ is... which you have not explained. Is the view you have posted the _only_ query with a performance issue? Have you checked the query plan? I notice that it selects from two tables without any filters at all. What runs this query? If it is an application does it ever apply any filters? How long does it currently take and how many rows in each table? – Nick.Mc Dec 04 '18 at 04:34
  • You might find that creating a couple of persisted calculated columns helps (or it may not). Also I notice a `TRY_CAST` and a `REPLACE` which makes it obvious you have data integrity / data cleansing issues. You should clean these up permanently. – Nick.Mc Dec 04 '18 at 04:39

0 Answers0