correlated Query-Performance Issues

Question

correlated Query-Performance Issues

jennifer zen 341

Hello,

I am trying to do a rolling 12 month sum using correlated query as below in the script, but it takes around 1 hour to get me the results.
Can somebody help me to script it better please?

select
[SalesEndingDate],
[Level],
[OrgName],
[SiteCode],
[SiteName],
[ItemDesc],
[ItemValue]

--Calculate rolling 12 months value for each site
, (
Select Sum([ItemValue])
FROM [Sales].[dbo].[MonthlyNationalValues] as Rolling12
WHERE cite.[SiteCode] = Rolling12.[SiteCode]
AND Rolling12.[SalesEndingDate] between dateadd("m", -11, cite.[SalesEndingDate]) and cite.[SalesEndingDate]
AND cite.[SalesEndingDate] >'2020-03-31'
AND [level] in ('04SPR','03DEW','04MWRT')
AND[ItemDesc] ='SER2393'
) as [12MonthRollingItemValue]

from
[Sales].[dbo].[MonthlyNationalValues] cite

where 1=1
and [level] in ('04SPR','03DEW','04MWRT')
and [SalesEndingDate] >'2020-03-31'
and [ItemDesc] = 'SER2393'

Thanks in advance

Ronen Ariely 15,221 Reputation points

2021-10-12T01:03:18.727+00:00

Good day jennifer and welcome to the QnA forum,

In order to discuss about queries performance we need you to provide the information to reproduce the scenario (queries to create the relevant table(s) and all the relation to other tables indexes, constraints and so on - since all these can impact the performance best query)

You should understand that SQL Server doesn't execute the query as we write it. The service first parse the query and build multiple Execution Plans to get the result. Next it choose one of these execution plans according to your specific configuration, according to your specific table and related entity and according to your specific data using statistics which it stores)

The minimum to start a discussion is to have a table with the same structure and get your real Exception Plan when you execute the query in your server. Note that we need the full XML execution plan and not only a screenshot for better insights.

Please provide the missing information to reproduce the table and several rows for sample.

1) Queries to CREATE your table(s) including indexes
2) Queries to INSERT sample data.
3) your real Execution Plan
EchoLiu-MSFT 14,626 Reputation points

2021-10-15T02:47:48.163+00:00

Could you have any updates

1 answer

Your answer

Ronen Ariely 15,221 Reputation points

2021-10-12T01:03:18.727+00:00

Good day jennifer and welcome to the QnA forum,

In order to discuss about queries performance we need you to provide the information to reproduce the scenario (queries to create the relevant table(s) and all the relation to other tables indexes, constraints and so on - since all these can impact the performance best query)

You should understand that SQL Server doesn't execute the query as we write it. The service first parse the query and build multiple Execution Plans to get the result. Next it choose one of these execution plans according to your specific configuration, according to your specific table and related entity and according to your specific data using statistics which it stores)

The minimum to start a discussion is to have a table with the same structure and get your real Exception Plan when you execute the query in your server. Note that we need the full XML execution plan and not only a screenshot for better insights.

Please provide the missing information to reproduce the table and several rows for sample.

1) Queries to CREATE your table(s) including indexes
2) Queries to INSERT sample data.
3) your real Execution Plan
EchoLiu-MSFT 14,626 Reputation points

2021-10-15T02:47:48.163+00:00

Could you have any updates

Answer 1

EchoLiu-MSFT 14,626

Hi @jennifer zen ,

Do you have the right index? Please try to create an index on the conditional column([level]、[SalesEndingDate]、[ItemDesc]) after WHERE first.

In addition, we need you to provide the execution plan and index information on your table.

Regards
Echo

0 comments

Share via

correlated Query-Performance Issues

1 answer

Your answer