How do I get to my universe to do joins in a particular order? I want to join a table for which I am providing filtering criteria resulting in a few hundred records to a table to very large table.
This is the query I am getting…
SELECT a.SomeObject, b.SomeOtherObject
FROM BigTable b
INNER JOIN SmallerTable a on b.ID = a.ID
WHERE a.ID = ‘X’
What I want is…
SELECT a.SomeObject, b.SomeOtherObject
FROM SmallerTable a
INNER JOIN BigTable b on a.ID = b.ID
WHERE a.ID = ‘X’
We are on BOE 4.2 SP07 Patch 4. My database is SQL Server 2016.
I am aware that logically the join order doesn’t matter when using INNER joins. This is a simplified example. My real query has both a mixture of INNER, LEFT, and RIGHT joins. I can write the query in SQL Server and get it to run in a few seconds, but I have not yet been able to tweak the universe to get it to generate the query that way.
I may have issues beyond BusinessObjects / IDT here. This article suggests that the join order does matter for performance, but the SQL Server optimizer decides for itself what to do unless you get really heavy handed and to which I am not sure I want to resort.
It’s always been my understanding that the Business Objects Query optimizer order queries from the table with the most rows towards the tables with lesser rows.
Have you looked at the Data Access Guide? I found the one for BI 4.2sp7. I don’t think a login is required to access it. There might be something that can be modified in the connection config files for what you are trying to achieve.
You might also want to look at your universe connection properties. The Array Fetch Size could be different between Business Objects and your query tool. I was just testing some reports and a change in the Array Fetch Size from the default of 10 to 100 cut the report processing time by an average of half.
The problem here is I do not want to risk affecting a bunch (250+) existing reports.
I was trying to add the objects related to the “SmallTable” to an existing universe with the objects related to the “BigTable”. I have changed course and am adding the objects I need related to the “BigTable” to an existing universe with objects related to the “SmallTable”. Far fewer reports based on this universe and I can update the joins to what I want them to be such that the join order doesn’t matter.
Certainly not the solution I was looking for, but it will work.
Can’t you use the optimizer of the MSSQL server. Normally SQL statements are adjusted by an database optimizer, so even if you see this specific SQL statement in the result of the WebIntelligence generated SQL doesn’t say that the database will perform it that way.