Re: Performance question and SQL SERVER - RESOLVED

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hi Peter

Excellent news!

IBM strongly suggests not using the QAQQINI in QUSERSYS - maybe it get 
updated at updates, and it is probably good to isolate the effects of 
these settings.

It does seem, in your case, to be a general benefit - YMMV.

I don't have a citation that says why leave the one in QUSRSYS alone - 
others may have it.

Vern

On 6/4/2013 7:42 PM, Peter Connell wrote:
> Turns out that all I had to do is set one ini file entry to a non-default value.
> UPDATE QUSRSYS/QAQQINI SET QQVAL='*ONLY_REQUIRED' WHERE QQPARM ='REOPTIMIZE_ACCESS_PLAN'
> Now all 300 SQL requests complete in just under 5 seconds.
> That's so blindingly fast that even after writing a C program that runs them all simultaneously via IBM's multi-thread API it still can't do it any faster than 5 seconds.
> I'm looking for a reason why I should use a library other than QUSRSYS since it may be that all SQL requests from any other jobs may benefit as well.
>
> Peter
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
> Sent: Saturday, 1 June 2013 6:48 a.m.
> To: midrange-l@xxxxxxxxxxxx
> Subject: Re: Performance question and SQL SERVER
>
> On 29 May 2013 13:10, Peter Connell wrote:
> > Tried that. DEBUG shows that the ODP is deleted as soon as you SQL
> > delete the records.
>     The /fastdelete/ feature will effectively DROP the TABLE just like how one might DROP and CREATE of a work file.  Avoid or disable [as I recall, there is an INI option\setting] that fast-delete feature for that DELETE request, if the change from DROP\CREATE to using DELETE effected the same loss of re-use.
>
> > I intend to try JDBCR4.
>     By shifting from the current purely dynamic requests to another purely dynamic interface, I would not expect to see much difference.
>
> > <<SNIP>>
> Regards, Chuck
>
> > Gary Thompson on Thursday, 30 May 2013 7:56 a.m. wrote:
> >
> > > maybe try:
> > > instead of a new work file try same file but use SQL to delete all
> > > rows so you just load/clear/load/clear etc also any suggestions from
> > > System i Navigator index advisor ?
> > >
> > > Peter Connell on Wednesday, May 29, 2013 1:48 PM wrote:
> > >
> > > > <<SNIP>> when a new work file is created for the next client
> > > > transaction, these ODPs get deleted and the transaction is slow
> > > > again. <<SNIP>>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
>
> #####################################################################################
>
> This correspondence is for the named person's use only. It may contain confidential
> or legally privileged information, or both. No confidentiality or privilege is waived
> or lost by any mistransmission. If you receive this correspondence in error, please
> immediately delete it from your system and notify the sender. You must not disclose,
> copy or rely on any part of this correspondence if you are not the intended recipient.
> Any views expressed in this message are those of the individual sender, except where
> the sender expressly, and with authority, states them to be the views of Veda.
> If you need assistance, please contact Veda on either :-
> Australia 1300-762-207 or New Zealand +64 9 367 6200