Блог

Контекст:

Время выполнения теперь составляет 2 минуты. Я использую Azure с эластичной базой данных 50 eDTU
Вопрос:
Есть ли какой-либо способ ускорить этот запрос? Я пытаюсь включить индекс #ActualValuesLines , вместо ОБЪЕДИНЕНИЯ INSERT INTO , но время все еще остается 2 минуты.

 DECLARE @RoNo int
CREATE TABLE #ReportValues(Position int, Col nvarchar(100), Amount decimal(28, 10))

CREATE TABLE #ActualValuesLines 
(Amount decimal(28,10),  
Date datetime2(7),  
DimensionElement0Id int,
DimensionElement1Id int,
DimensionElement2Id int,
DimensionElement3Id int,
DimensionElement4Id int,
DimensionElement5Id int,
DimensionElement6Id int,
DimensionElement7Id int,
DimensionElement8Id int,
DimensionElement9Id int,
AverageRate decimal(28,10),
Text0 nvarchar(max),
Text1 nvarchar(max),
Text2 nvarchar(max),
Text3 nvarchar(max),
Text4 nvarchar(max),
Text5 nvarchar(max),
Text6 nvarchar(max),
Text7 nvarchar(max),
Text8 nvarchar(max),
Text9 nvarchar(max))

INSERT INTO #ActualValuesLines(Amount, Date, DimensionElement0Id, DimensionElement1Id, DimensionElement2Id, DimensionElement3Id, DimensionElement4Id, DimensionElement5Id, DimensionElement6Id, DimensionElement7Id, DimensionElement8Id, DimensionElement9Id, AverageRate, Text0, Text1, Text2, Text3, Text4, Text5, Text6, Text7, Text8, Text9) 
SELECT Amount, Date, DimensionElement0Id, DimensionElement1Id, DimensionElement2Id, DimensionElement3Id, DimensionElement4Id, DimensionElement5Id, DimensionElement6Id, DimensionElement7Id, DimensionElement8Id, DimensionElement9Id, AverageRate, Text0, Text1, Text2, Text3, Text4, Text5, Text6, Text7, Text8, Text9 FROM x WHERE Text0 is NULL


SET @rowNo = 1
INSERT #ReportValues(Position, Col, Amount)
SELECT @rowNo, 'January', -ISNULL(Sum(Amount), 0)
FROM #ActualValuesLines ActualValuesLines
    LEFT JOIN DimensionElements LineName ON ActualValuesLines.DimensionElement4Id = LineName.Id
WHERE (LineName.Name = 'Goods')
    AND Year(ActualValuesLines.Date) = 2020 AND Month(ActualValuesLines.Date) =  1

INSERT #ReportValues(Position, Col, Amount)
SELECT @rowNo, 'Febuary', -ISNULL(Sum(Amount), 0)
FROM #ActualValuesLines ActualValuesLines
    LEFT JOIN DimensionElements LineName ON ActualValuesLines.DimensionElement4Id = LineName.Id
WHERE (LineName.Name = 'Goods')
    AND Year(ActualValuesLines.Date) = 2020 AND Month(ActualValuesLines.Date)= 2

...

SET @rowNo = 2
INSERT #ReportValues(Position, Col, Amount)
SELECT @rowNo, 'January', -ISNULL(Sum(Amount), 0)
FROM #ActualValuesLines ActualValuesLines
    LEFT JOIN DimensionElements LineName ON ActualValuesLines.DimensionElement4Id = LineName.Id
WHERE (LineName.Name = 'STTS')
    AND Year(ActualValuesLines.Date) = 2020 AND Month(ActualValuesLines.Date) =  1

INSERT #ReportValues(Position, Col, Amount)
SELECT @rowNo, 'Febuary', -ISNULL(Sum(Amount), 0)
FROM #ActualValuesLines ActualValuesLines
    LEFT JOIN DimensionElements LineName ON ActualValuesLines.DimensionElement4Id = LineName.Id
WHERE (LineName.Name = 'STTS')
    AND Year(ActualValuesLines.Date) = 2020 AND Month(ActualValuesLines.Date)= 2

...

SET @rowNo = 100
INSERT #ReportValues(Position, Col, Amount)
SELECT @rowNo, 'January', -ISNULL(Sum(Amount), 0)
FROM #ActualValuesLines ActualValuesLines
    LEFT JOIN DimensionElements LineName ON ActualValuesLines.DimensionElement4Id = LineName.Id
WHERE (LineName.Name = 'STTS')
    AND Year(ActualValuesLines.Date) = 2020 AND Month(ActualValuesLines.Date) =  1

INSERT #ReportValues(Position, Col, Amount)
SELECT @rowNo, 'Febuary', -ISNULL(Sum(Amount), 0)
FROM #ActualValuesLines ActualValuesLines
    LEFT JOIN DimensionElements LineName ON ActualValuesLines.DimensionElement4Id = LineName.Id
WHERE (LineName.Name = 'STTS')
    AND Year(ActualValuesLines.Date) = 2020 AND Month(ActualValuesLines.Date)= 2

SELECT Position, Col, Amount FROM #ReportValues
DROP TABLE #ReportValues