Categories
Performance issue -- order of tables in the query
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?
Best Answer
-
@Chris Arnold - This is not an optimal or suggested method. Performing patch merges would be much easier and may help you overcome this challenge.
Reference:
Other option would be to use the RPD to be migrated and update only the connection pools to point to the required target DB.
1
Answers
-
@Chris Arnold - Please check the list of patches applied in both environments and make sure there is no difference. This is assuming both environments are on same versions OAS 2023. As you confirm the same RPD is being used, the only chance there could be difference in query generation is only if they are on different versions of bundle patches or missing some interim patch etc…
0 -
I am going to have an admin run an OPatch Inventory on all tiers and see what we come up with. I'll report back.
Thank you!
1 -
Use 'opatch lspatches' for the compact list which make comparison easier.
In addition, assuming the RPD's are the exact same copies….RPD Compare
check- the session logs to make sure some compatibility mode, or other parameters are not added in one environment vs. the other.
- check obis.properties
- check NQSConfig.INI
- Check OAS Console > System Settings
- Lastly, if querying two different SQL Server and not the same., then you may need to cross-check (understanding you have identified query generation).
I'm probably missing something off the top, so, other comments, welcomed.
0 -
We've done an inventory of patches across all of our environments, and all are the same — which is good.
It would seem that our issue is actually being caused by the way we 'promote' changes from one environment to the next. (In the past this way has worked for us — but since upgrading from 6.4 to 7.0 recently, this seems to have become a problem) The way we do it is…
We make a change in our RPD in the DEV environment. Once we are ready to 'promote' that change to the next tier (we'll say PROD for this case), we then go into an offline version of the PROD rpd and copy the Physical Catalog from the DEV RPD and paste it into PROD RPD. We then deploy the PROD RPD.
For whatever reason, doing it this way causes the generated query to reference the tables in a different order.
I'm guessing we need to figure out another way to promote our RPD changes from one environment to another.*Note - RPD version for all environments is 12.3.6.1.0
1 -
Because you are on OAS 2023 already, how about upgrading to OAS 2024?
You could then consider using the Semantic Modeler instead of the Model Administration Tool.
Just make sure your RPD is "compatible" (currently only relational sources are supported), but if it is, it will make your release / promotion of code a lot easier because it would mostly be done via git releases, tags, branching (based on your own methodology in handling versioning).
And with the connection credentials being outside the semantic model, you will also not have to worry about updating passwords: the same connection identifier will reference different databases in your DEV and in your PROD environment.
Just remember it is a "new" tool, therefore it will require some time to adapt to it, but it could also help you if you have multiple developers etc.
Have a look at least (repeating myself: as long as your RPD is compatible with it).
0
