RE: Performance question and SQL SERVER

Vern,
Interesting feed-back. I'll take all that on board.

Thanks, Peter

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Thursday, 30 May 2013 2:01 p.m.
To: Midrange Systems Technical Discussion
Subject: Re: Performance question and SQL SERVER

Hi Peter

I'm going to suggest a lot of work, but hey, you're doing that already, right?

Index Advisor is one thing. Visual Explain can help even more. Take a look at the access plans. They'll show you lots of stuff. Oh yeah, which version are you on? Of the OS and of Navigator?

As to which engine is used, VE tells you which one and why. Take a look at all that stuff on the right when you have clicked on the leftmost part of the tree - the final result.

EVIs are used for selectivity only - no ordering. So they apply to WHERE clauses and to JOIN criteria. They are often used in bitmap processing of statements.

VE also has some recommended index information in that rightmost area - at each node, in fact, as I recall.

If you have something using a table scan, there are reasons, and some can be overcome.

Doing all this stuff in RUNSQLSTM can mean optimization all the time, as you suggested. Maybe create views, as someone said - I think the access plan is stored with those, so less need for optimization. If the statements are pretty short-running, optimization becomes a major portion of the time to run them.

HTH
Vern

On 5/29/2013 6:34 PM, Peter Connell wrote:

Just upgraded to Power 7 and batch stuff runs about 3 times faster.
This stuff is slow in batch and interactive but CPU shoots right up in the 90s when it runs.
Work file has indexes as per Index Advisor.
Heard of but wouldn't know where to start with EVIs.
Don't know if it's CQE or SQE or how to change that as a factor.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Sam_L
Sent: Thursday, 30 May 2013 11:27 a.m.
To: Midrange Systems Technical Discussion
Subject: Re: Performance question and SQL SERVER

Clutching at straws here...

Is there enough memory in the sub system? No excessive paging showing up during the 1 minute?
Any excessive disk activity show up during the 1 minute run?
Any indexes or EVIs on the work file?
Batch or interactive? Batch may perform better based on time slice.

And which OS version?

Is maybe the CQE being invoked instead of the SQE?

Sam

On 5/29/2013 3:48 PM, Peter Connell wrote:

I've have an SQL script that runs considerably faster on SQL Server that than on system i.

I have 300 SQL statements which must be run each time a transaction is requested by the client and the response is slow. It is problematic to convert these 300 to native RPGLE which I know would perform well.
The client makes a usual inquiry against a consumer database and a report is generated from matching detail entries on several database files. The report was developed long ago with RPGLE.
But now the RPGLE creates a work file of a few hundred values deemed as significant which are derived from the same data source. The work file is repopulated for each client transaction and its content depends on each new client request, so it differs each time.

Each of the 300 SQL statements (which in fact are supplied by a 3rd party) defines a separate business characteristic that uses SQL to join the work file entries to a predefined table of attributes and return a summary result value.
The net result is that, in addition to the report, a further 300 characteristic values can be returned that relate to specific client request.
Unfortunately, all 300 statements can take about a minute to complete which is too slow. However, if the same transaction is repeated then the same result can be returned in about 5 seconds.
Diagnostics from running in debug mode show that a repeated request reuses ODPs. Unfortunately, when a new work file is created for the next client transaction, these ODPs get deleted and the transaction is slow again.

I've tried playing around with activation groups , shared opens via OVRDBF and using an running a pre-requisite OPNDBF but had no success in reducing the transaction time.
I am perplexed by the fact that we have a developer familiar with SQL server who has demonstrated that he can easily create a script that runs all 300 statements on SQL server in just a few second with no performance problems. Why is that?

Peter

--
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.
7
#####################################################################################

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