FW: Begin-Select performance using 4 table Join

["Simonian, Guy" <SimonianG@AETNA.COM>]

Thanks for the pointer Steve.

Using -xp does have a difference on this app. and makes it run in a
second or two, rather than the minute or longer.

Since the query involved uses bind variables and dynamic query
variables, we were apparently creating far too many temporary stored
procedures, -xp suppressed that activity.


> ----------
> From:         Steve Cavill[SMTP:steve.cavill@SQRIBE.COM.AU]
> Sent:         Sunday, April 05, 1998 9:55 PM
> To:   Multiple recipients of list SQR-USERS
> Subject:      Re: Begin-Select performance using 4 table  Join
>
>
> 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.
>
>
>