Re: Begin-Select performance using 4 table Join

[Steve Cavill <steve.cavill@SQRIBE.COM.AU>]

Hi Guy,
It may be related to SQR buffering rows.
Try -b1 option on the begin-select.  This will ensure that SQR only
retrieves one row at a time.  The default is -b10.  Note that in production
it is better to increase the -b option to a large number to improve
performance.

Also the time taken to retrieve the first row includes the time taken to
establish the connection with the database.
You should try the -XP option to prevent SQR from creating temporary stored
procedures.

Does the whole report take 20 times longer or 57 seconds longer?  To really
measure the comparitive performance your SQR report should do nothing other
than the select.  You can use the -S option to check the exact SQL that SQR
is passing to Sybase.

Cheers, Steve.

Simonian, Guy wrote:

> Am running SQR V3.0.18.1 in a Sybase 11 environment.  The SQR client is
> on SunOS 5.5.1 and also on WinNT.  Sybase is under SunOS 5.5.1
>
> It takes over a minute for my query to find 1 join from the database,
> yet if I take the exact same SQL and run it from ISQL or RapidSQL, it
> takes about 3 seconds!  I've tried the -rs and -rt SQR options to run
> the compiled report, but the results are the same.
>
> ISQL:
> ------------------------------------------------------------------------
> ----------------
>
> select HRUTEST..PS_PDS_TRX_FACT.TRX_KEY, PLAN_YR, GROUP_INDIV_CODE
>          from HRUTEST..PS_PDS_TRX_FACT,HRUTEST..PS_PDS_PRODUCT,
>          HRUTEST..PS_PDS_POL_CASE, HRUTEST..PS_PDS_PAYROLL where
>          HRUTEST..PS_PDS_TRX_FACT.AA_KEY              = 4000 and
>
>          HRUTEST..PS_PDS_TRX_FACT.PAYEE_PARTY_KEY     =
>          HRUTEST..PS_PDS_TRX_FACT.PRODUCER_PARTY_KEY and
>          HRUTEST..PS_PDS_PRODUCT.PRODUCT_KEY      =
>          HRUTEST..PS_PDS_TRX_FACT.PRODUCT_KEY and
>          HRUTEST..PS_PDS_POL_CASE.POLICY_CASE_KEY =
>          HRUTEST..PS_PDS_TRX_FACT.POLICY_KEY and
>          HRUTEST..PS_PDS_TRX_FACT.PAYROLL_KEY         =
>          HRUTEST..PS_PDS_PAYROLL.PAYROLL_KEY and    4000   =
>          HRUTEST..PS_PDS_PAYROLL.DISBURSEMENT_KEY
>
> returns 3 rows in 2 seconds
> SQR SOURCE:
> ------------------------------------------------------------------------
> -------------------------
>
> !------------------------------------------------------------/
> !       P R O C E D U R E   xfer.grp.ind         /
> !------------------------------------------------------------/
> begin-procedure xfer.grp.ind
> #debug show '@xfer.grp.ind, key, aa codes ' &AGY_AGT_KEY
> #debug show '@xfer.grp.ind, DISB key, '  #DISBURSEMENT_KEY
>
> begin-select on-error=stmnt-bad-exec
> {FACT}.TRX_KEY &TRX_KEY
> PLAN_YR &PLAN_YR
> GROUP_INDIV_CODE &GROUP_INDIV_CODE
>         let $detail.PLAN_YR = &PLAN_YR
>         let #TRX_KEY = &TRX_KEY
>         if &PLAN_YR = '1'
>                 if &GROUP_INDIV_CODE= 'I'
>                 let $sub_section='<!!a-de-lf-pp-fy-in.2>'
>                 else
>                 let $sub_section='<!!a-de-lf-pp-fy-gr.2>'
>                 end-if
>         else
>                 if &GROUP_INDIV_CODE= 'I'
>                 let $sub_section='<!!a-de-lf-pp-re-in.2>'
>                 else
>                 let $sub_section='<!!a-de-lf-pp-re-gr.2>'
>                 end-if
>         end-if
> #debug show '@xfer.grp.ind FOUND ONE XXXXXXXXXXXXXXXX' &TRX_KEY
>         let #event.type = #event.type + 1
>         do xfer.policy.comp.type
> from {FACT},{PRODUCT}, {POL_CASE}, {PAYROLL}
> where             {FACT}.AA_KEY              = &AGY_AGT_KEY
> and           {FACT}.PAYEE_PARTY_KEY     = {FACT}.PRODUCER_PARTY_KEY
> and           {PRODUCT}.PRODUCT_KEY      = {FACT}.PRODUCT_KEY
> and           {POL_CASE}.POLICY_CASE_KEY = {FACT}.POLICY_KEY
> and           {FACT}.PAYROLL_KEY         = {PAYROLL}.PAYROLL_KEY
>
> and           #DISBURSEMENT_KEY                  =
> {PAYROLL}.DISBURSEMENT_KEY
> end-select
> end-procedure
>
> ------------------------------------------------------------------------
> -----------------------
> This procedure takes over a minute to find the first record.


begin:          vcard
fn:             Steve Cavill
n:              Cavill;Steve
org:            SQRIBE Technologies
adr;dom:        1/14 Aquatic Drive;;;Frenchs Forest;;;
email;internet: steve.cavill@sqribe.com.au
title:          Technical Services Manager
x-mozilla-cpt:  ;0
x-mozilla-html: TRUE
version:        2.1
end:            vcard