Increase in presentation time with Outer Joins

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

Thanks


mkumar (BOB member since 2002-08-26)

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)

Cheers,
Mark

There are 4 PRM files in

C:\Program Files\Business Objects\BusinessObjects Enterprise 6\dataAccess\RDBMS\connectionServer\sybase

folder.
These are

Sybase.prm
Sybase11.prm
Sybase11en.prm
Sybaseen.prm

These are respective contents.

Sybase.prm

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE DBParameters SYSTEM "../dbparameters.dtd"><DBParameters>
	<Configuration>
		<Parameter Name="SORT_BY_NO">NO</Parameter>
		<Parameter Name="GROUPBYCOL">NO</Parameter>
		<Parameter Name="EXT_JOIN">YES</Parameter>
		<Parameter Name="EXT_JOIN_INVERT">YES</Parameter>
		<Parameter Name="LEFT_OUTER"></Parameter>
		<Parameter Name="RIGHT_OUTER"></Parameter>
		<Parameter Name="CONCAT">+</Parameter>
		<Parameter Name="UNION">UNION</Parameter>
		<Parameter Name="UNION_IN_SUBQUERY"></Parameter>
		<Parameter Name="INTERSECT"></Parameter>
		<Parameter Name="INTERSECT_IN_SUBQUERY"></Parameter>
		<Parameter Name="MINUS"></Parameter>
		<Parameter Name="MINUS_IN_SUBQUERY"></Parameter>
		<Parameter Name="OWNER">Y</Parameter>
		<Parameter Name="QUALIFIER">Y</Parameter>
		<Parameter Name="COMMA">+' '+</Parameter>
		<Parameter Name="REFRESH_COLUMNS_TYPE">Q</Parameter>
		<Parameter Name="CHECK_OWNER_STATE">N</Parameter>
		<Parameter Name="CHECK_QUALIFIER_STATE">N</Parameter>
		<Parameter Name="KEY_INFO_SUPPORTED">Y</Parameter>
		<Parameter Name="OUTERJOINS_GENERATION">ANSI_92</Parameter>
		<Parameter Name="USER_INPUT_DATE_FORMAT">'mm/dd/yyyy hh:m:s am/pm'</Parameter>
		<Parameter Name="USER_INPUT_NUMERIC_SEPARATOR">.</Parameter>
	</Configuration>

Sybase11.prm

	<Configuration>
		<Parameter Name="SORT_BY_NO">NO</Parameter>
		<Parameter Name="GROUPBYCOL">NO</Parameter>
		<Parameter Name="EXT_JOIN">YES</Parameter>
		<Parameter Name="LEFT_OUTER">$*</Parameter>
		<Parameter Name="RIGHT_OUTER">*$</Parameter>
		<Parameter Name="CONCAT">+</Parameter>
		<Parameter Name="UNION">UNION</Parameter>
		<Parameter Name="UNION_IN_SUBQUERY"></Parameter>
		<Parameter Name="INTERSECT"></Parameter>
		<Parameter Name="INTERSECT_IN_SUBQUERY"></Parameter>
		<Parameter Name="MINUS"></Parameter>
		<Parameter Name="MINUS_IN_SUBQUERY"></Parameter>
		<Parameter Name="OWNER">Y</Parameter>
		<Parameter Name="QUALIFIER">Y</Parameter>
		<Parameter Name="COMMA">+' '+</Parameter>
		<Parameter Name="REFRESH_COLUMNS_TYPE">Q</Parameter>
		<Parameter Name="CHECK_OWNER_STATE">N</Parameter>
		<Parameter Name="CHECK_QUALIFIER_STATE">N</Parameter>
		<Parameter Name="KEY_INFO_SUPPORTED">Y</Parameter>
		<Parameter Name="OUTERJOINS_GENERATION">USUAL</Parameter>
		<Parameter Name="USER_INPUT_DATE_FORMAT">'mm/dd/yyyy hh:m:s am/pm'</Parameter>
		<Parameter Name="USER_INPUT_NUMERIC_SEPARATOR">.</Parameter>
	</Configuration>

I could not find any useful information in Sybase11en.prm and sybaseen.prm


mkumar (BOB member since 2002-08-26)

There are two PRM files in

C:\Program Files\Business Objects\BusinessObjects Enterprise 6\dataAccess\RDBMS\legacy\sybase

also.
These are

Sybaseen.prm
sybase11en.prm

Sybaseen.prm

[RDBMS]
(GENERAL)
STG= sybaseen
SORT_BY_NO=NO
GROUPBYCOL=NO
EXT_JOIN=YES
EXT_JOIN_INVERT=YES
LEFT_OUTER=
RIGHT_OUTER=
CONCAT=+
UNION=UNION
UNION_IN_SUBQUERY=
INTERSECT=
INTERSECT_IN_SUBQUERY=
MINUS=
MINUS_IN_SUBQUERY=
OWNER=Y
QUALIFIER=Y
COMMA=+' '+
REFRESH_COLUMNS_TYPE=Q
CHECK_OWNER_STATE=N
CHECK_QUALIFIER_STATE=N
KEY_INFO_SUPPORTED=Y
OUTERJOINS_GENERATION=ANSI_92
USER_INPUT_DATE_FORMAT='mm/dd/yyyy hh:m:s am/pm'
USER_INPUT_NUMERIC_SEPARATOR=.

sybase11en.prm

[RDBMS]
(GENERAL)
STG= sybaseen
SORT_BY_NO=NO
GROUPBYCOL=NO
EXT_JOIN=YES
LEFT_OUTER=$*
RIGHT_OUTER=*$
CONCAT=+
UNION=UNION
UNION_IN_SUBQUERY=
INTERSECT=
INTERSECT_IN_SUBQUERY=
MINUS=
MINUS_IN_SUBQUERY=
OWNER=Y
QUALIFIER=Y
COMMA=+' '+
REFRESH_COLUMNS_TYPE=Q
CHECK_OWNER_STATE=N
CHECK_QUALIFIER_STATE=N
KEY_INFO_SUPPORTED=Y
OUTERJOINS_GENERATION=USUAL
USER_INPUT_DATE_FORMAT='mm/dd/yyyy hh:m:s am/pm'
USER_INPUT_NUMERIC_SEPARATOR=.

mkumar (BOB member since 2002-08-26)

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?


Anita Craig :us: (BOB member since 2002-06-17)

Take a copy of your sybase11en.prm file.
Now change the following values in it:


LEFT_OUTER= 
RIGHT_OUTER= 
OUTERJOINS_GENERATION=ANSI_92 

to read like the ones above (as per the Sybaseen.prm file)

Let me know how you get on.

Cheers,
Mark

Yes

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. :roll_eyes:

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.


mkumar (BOB member since 2002-08-26)

Changes in PRM file, do make an effect on Query fetch time.
But this has no effect on the Computation time.

Somehow the computation is always much more with outer joins.

                         Nbr of Rows           Fetch Time  Computation time
Query 1 Inner Join       115000                 8                      4
Query 1 Outer Join       135000                 12                    14
Query 2 Inner Join       155000                 16                     4
Query 2 Outer Join       185000                 22                    15

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 (BOB member since 2002-08-26)

UPDATE:

This has been escalated as a Bug by Tech Support. I will post the bug id as soon as it is reported.


mkumar (BOB member since 2002-08-26)

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.


Anita Craig :us: (BOB member since 2002-06-17)

I tried it on BO 6.0 and BO 6.1a.

I will post the bug id as soon as I hear from them.


mkumar (BOB member since 2002-08-26)

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?


Nats (BOB member since 2011-05-24)

Uhh reviving a zombie thread :shock:


Andreas :de: (BOB member since 2002-06-20)