OAS version: 2023 (7.0.0.0.230710)
SQL Server version: SQL Server 2016
Operating System: Microsoft Windows x64 (64-bit)
We are running into a weird issue where many of our reports runs well in a lower tier environment, but when we try to run those same reports in the Production tier the same reports just spin & never return data. Through investigating we are seeing that the performance issue is caused by the way the RPD generates the query being sent to the server. I will try to explain...
In the DEV tier, the query being generated might go something like this:
Select D1.Attr1,
D1.Attr2,
D2.Attr1,
D2.Attr2,
D3.Attr1,
D3.Attr2,
SUM(F1.Aggr1),
SUM(F1.Aggr2)
From Dimension1 as D1,
Dimension2 as D2,
Dimension3 as D3,
Fact1 as F1
Where D1.pkey1 = F1.fkey1
and D2.pkey2 = F1.fkey2
and D3.pkey3 = F1.fkey3
and D1.Attr1 = 'A'
and D2.Attr1 = 'A'
and D3.Attr1 = 'A'
Group by D1.Attr1,D1.Attr2,D2.Attr1,D2.Attr2,D3.Attr1,D3.Attr2
The above query runs very fast in our DEV environment. But when we try to run the same report in PROD, the order of the tables being queried actually gets changed, and it kills our performance. It is using the exact same RPD configuration (as best I can tell). The PROD RPD generates the query as such:
Select D1.Attr1,
D1.Attr2,
D2.Attr1,
D2.Attr2,
D3.Attr1,
D3.Attr2,
SUM(F1.Aggr1),
SUM(F1.Aggr2)
From Dimension1 as D1,
Dimension3 as D3,
Dimension2 as D2,
Fact1 as F1
Where D1.pkey1 = F1.fkey1
and D2.pkey2 = F1.fkey2
and D3.pkey3 = F1.fkey3
and D1.Attr1 = 'A'
and D2.Attr1 = 'A'
and D3.Attr1 = 'A'
Group by D1.Attr1,D1.Attr2,D2.Attr1,D2.Attr2,D3.Attr1,D3.Attr2
In the above PROD query, you'll notice that the order of Dimension2 & Dimension3 in the 'From' clause have changed.
We are not sure why this table order is changing, nor how to dictate the table order when the query is generated.
We know it is the order of the tables in the query that is causing the performance problem because if we simply take the DEV query and manually run it in the PROD environment (via Sql Server Management Studio), the query returns rows very fast -- but when we run the PROD query through SSMS, it just runs forever & never returns data.
My hope is that there is some setting we are missing in the RPD (or maybe even in the OAS config files?...maybe a DB setting?) that will help us to better control (and correct) this behavior. Can anyone offer any insight on this issue?
