RE: [sqr-users] Performance difference v3 vs v9

["Peter Burton" <peter.burton@oracle.com>]

Luc,

Could be the license logic but that is not something that can be controlled by 
any setting available to the user.

You do not need to provide a database connection for those reports which do not 
require one.  Simply specify "/" for the connectivity and specify the -XL 
command line flag.

        sqr hello / -xl

Peter

-----Original Message-----
From: sqr-users-bounces+peter.burton=oracle.com@sqrug.org 
[mailto:sqr-users-bounces+peter.burton=oracle.com@sqrug.org] On Behalf Of Luc 
Van der Veurst
Sent: Wednesday, July 11, 2007 7:41 AM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Performance difference v3 vs v9


Hello,

Peter, changing the -B parameter doesn't help.


> If the difference is always 10s, then it looks more like a startup problem
than a sql perf problem (i.e. the increase is not linear)

Yes, that's what I thought too. It certainly is not a database sql
performance problem since the database is the same for both versions.

The increase is not linear, always about 9 to 10 seconds slower, no
matter how long the report runs.

My sqr program looks like :

$ cat sqrworld.sqr
begin-report
show 'Hello World.'
end-report
$

I execute the program with the unix time command.

executed with v3 :

SQR: Structured Query Report Writer V3.0.7.0.1
Copyright (C) MITI, 1994, 1995.  All Worldwide Rights Reserved.

Hello World.

SQR: End of Run.

real    0m0.03s
user    0m0.02s
sys     0m0.01s

executed with v9 :

Hyperion System 9 BI+ Production Reporting Server - 9.3.0.1.0.836
Copyright (c) 1994-2007 Hyperion Solutions Corporation.  All Rights Reserved.

(SQR 8906) Warning:  The product license will expire on 20-aug-2007.
Hello World.

SQR: End of Run.

real    0m9.47s
user    0m0.61s
sys     0m0.23s


Could it be because this is a test license ?
The time is spent between printing the copyright line and the
warning about the expiration date (I don't know if that output
is buffered).

(There is a difference between 3 and 9: even when the program doesn't
need a database connection, v 9 establishes a connection while 3 doesn't
(I know because I have to provide valid username/password/server information
for v9 and not for v3). If the program requires a database connection,
the time difference between the 2 is also between 9 and 10 seconds).

Thanks,
Luc.





> i.e. a 0.07s report now takes 10.81s/   Does a 100s report take 110s?
> How long does a report that returns 0 rows take?
> You could trace the SQR execution time by placing a datenow() function as the
first as last commands of the program.
> Steve.
>
> -----Original Message-----
> From: sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org
> [mailto:sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org] On Behalf
Of Peter Burton
> Sent: Tuesday, 10 July 2007 10:04 PM
> To: This list is for discussion about the SQR database reporting languagefrom
Hyperion Solutions.
> Subject: RE: [sqr-users] Performance difference v3 vs v9
>
> Luc,
>
> You might try changing the fetch buffer row value (-Bnn).  Other than passing
this value to the CT-Lib software this is not used by
> SQR.  The default value for SQR 9.3 is 100.  Earlier versions of SQR used
different values.
>
> Peter
>
> -----Original Message-----
> From: sqr-users-bounces+peter.burton=oracle.com@sqrug.org
[mailto:sqr-users-bounces+peter.burton=oracle.com@sqrug.org] On Behalf Of
> Luc Van der Veurst
> Sent: Tuesday, July 10, 2007 7:25 AM
> To: sqr-users@sqrug.org
> Subject: [sqr-users] Performance difference v3 vs v9
>
>
> Hello,
>
> We are still running sqr version V3.0.7.0.1 :-).
>
> Since re-investigating in what report generation tool we will use in the
future will be on next year's budget, we wondered if our
> sqr code would sitll run under v9.
>
> Therefore, we installed version 9.3.0.1.0.836.
>
> Our reports still work, so that was the good news, but version 9 is much
slower than version 3.
>
> A report that runs with v3 in 0.07s seconds takes 10.81s in v9. The report
just selects one column from one tuple in the database.
>
> We see this difference in all our reports: they take about 10 seconds more to
run.
>
> Our environment :
>
> OS : Soralis 8
> DB : Sybase ASE 12.5.3
>
> Hyperion says that the difference is due to the fact that
> v3 uses dblib while v9 uses ctlib.
> I find this hard to believe, we have other dblib and ctlib programs to
compare.
>
> What I did notice is that sybase's libraries are linked statically into sqr v3
while there are more libraries dynamically loaded in
> v9 :
>
> v3 :
>
> $ ldd /local/sybase/workbench_30/bin/sqr
>         libnsl.so.1 =>   /lib/libnsl.so.1
>         libc.so.1 =>     /lib/libc.so.1
>         libdl.so.1 =>    /lib/libdl.so.1
>         libmp.so.2 =>    /lib/libmp.so.2
>         /usr/platform/SUNW,Ultra-4/lib/libc_psr.so.1
>
> v9 :
>
> $ ldd /local/sybase/Hyperion/BIPlus/bin/SQR/Server/Sybase/bin/sqr
>         libbtunicode.so =>
> /local/sybase/Hyperion/BIPlus/bin/SQR/Server/Sybase/lib/libbtunicode.so
>         libCrun.so.1 =>  /lib/libCrun.so.1
>         libC.so.5 =>     /lib/libC.so.5
>         libsocket.so.1 =>        /lib/libsocket.so.1
>         libnsl.so.1 =>   /lib/libnsl.so.1
>         libct.so =>      /local/sybase/sdk125/clt/lib/libct.so
>         libcs.so =>      /local/sybase/sdk125/clt/lib/libcs.so
>         libtcl.so =>     /local/sybase/sdk125/clt/lib/libtcl.so
>         libcomn.so =>    /local/sybase/sdk125/clt/lib/libcomn.so
>         libintl.so =>    /local/sybase/sdk125/clt/lib/libintl.so
>         libm.so.1 =>     /lib/libm.so.1
>         libdl.so.1 =>    /lib/libdl.so.1
>         libc.so.1 =>     /lib/libc.so.1
>         libthread.so.1 =>        /lib/libthread.so.1
>         libmp.so.2 =>    /lib/libmp.so.2
>         /usr/platform/SUNW,Ultra-4/lib/libc_psr.so.1
> $
>
>
> 1. Does anyone had similar experiences with performance after an
>    upgrade ?
>
> 2. Does anyone has relinked ldd so that more libraries are statically
>    linked ?
>
> Thanks,
> Luc.


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users