I was testing the effect of outer joins on performance.
I created two similar universe (One with OUTER joins and one with Equi joins)
I created similar reports on two universes.
I ran both the reports and noted the timings. Then I ran both the queries in DB Artisan and noted the timings
Nbr of Rows fetched with INNER join = 69
Nbr of Rows fetched with OUTER join = 95
Time (In secs) for BO report with INNER join = 9 sec
Time (In secs) for BO report with OUTER join = 23 sec
Time (In secs) for BO query in DB Artisan with INNER join = 9 sec
Time (In secs) for BO query in DB Artisan with OUTER join = 11 sec
If both the queries are run in DB Artisan there is little difference in timings however time difference is huge when reports are run in BO.
It the presentation layer in BO that is taking most of time. If I view the Data Manager it shows 11 seconds taken for the OUTER JOIN query to run.
WHY is presentation time so much in BO with outer joins even though the number of rows returned are not much? We are on Sybase 12.5
Can you find your PRM file for sybase and post the contents on here please. It will be located in the Data Access directory.
Just the first section of it will do (the one with the outer join details in)
Are you sure the difference is BusinessObjects versus DBArtisan?
If you want to compare timing, you need to make sure that everything on the server and network are identical. If someone else was running a big query on your database server at the same time as your outer query in BusinessObjects, that alone could cause you to get a slower response time.
You might need to repeat the experiment multiple times to ensure that it’s simply a difference in the tools.
Also - are you sure you’re running the identical SQL? Did you copy the Business-Objects-generated SQL and paste that into DBArtisan? Are you using the same database userid in both cases?
I did run reports and queries multiple (5) times. I was running BO report and query in DB Artisan consecutively so any load should have affected results at both places. I was following this sequence. Run 1
Run BO Report
Run Quiery in DB Artisan Run 2
Run BO Report
Run query in DB Artisan
… Run 5
…
I took the SQL query generated by BO and pasted in DB Artisan. I used the same userid to login to DB Artisan as in Universe connection.
I did my first testing on Friday and Monday. Today first thing in morning I made changes to PRM file and ran BO report. The output was amazing. I got back BO report in 11 secs. To verity the reason of improvement, I revert back the changes made in PRM file. Still I got the report in 11 seconds.
So I could not ascertain if it were the changes in PRM that improved but definitely there is improvement. This is actually strange. There is nothing happened at database side and all of a sudden (After modifying PRM file and reverting back) the presentation time has improved drastically. I am going to test the reports in some other machine and see how it works.
Do you have any comments on it.
This testing shows if the number of rows fetched increases with outer join, the computation time increases. But if the number of rows fetched increases with inner join (I joined one more table in Query2 with detailed data to get more rows) the computation time does not increase.
Any ideas?
mkumar – thank you for the update. It will be helpful to know the bug id. It will also be helpful to know which version of BusinessObjects you’re using.
Hello Again! this work,
the problema was
OUTERJOINS_GENERATION = ANSI92
is the correct form
but now i have this problem, the universe creat a sql with LEFT OUTER JOIN and the progress database only read LEFT JOIN.
Any idea?