Re: WS problems -- WEB400

Janne

You might not be doing anything wrong - the optimizer will make use of different resources if it decides the environment is different enough from the last time it looked. This includes available memory and CPU use and %-age and all manner of other things - size of tables, etc.

Did you run a monitor over the production run? If not, that would be a good idea - then look at the index advisor stuff.

I thought there is a setting, maybe in QAQQINI, to have the optimizer consider all access paths when working out the access plan - this would make the optimizer run a little longer - I did not see it in a more recent version.

I do see recovery text in message SQL4005 - Query optimizer timed out - that gives this advice, which, frankly, surprised me -

"To ensure an index is considered for optimization, specify the logical file of the index as the table to be queried. The optimizer will first consider the index of the logical file specified on the SQL select statement. Note that SQL created indexes cannot be queried. An SQL index can be deleted and recreated to increase the chances it will be considered during query optimization. Consider deleting any indexes no longer needed."

I'd always heard and advised myself never to use LFs as the FROM file in a SELECT, since the optimizer would still go to the PF and find the other indexes over it. This seems a variation of that, which might be useful in your case, if you can.

If your new index is an SQL index and not a DDS-built LF, you can't do this, as the recovery text says. At least, I think this is still true - but I wonder about SQL indexes with columns specified and with WHERE clauses.

Enough thoughts for an early Thursday - hope something helps!

Vern

On 7/23/2015 8:31 AM, Janne Lindh wrote:

Jim,
The index I wrote about in the latest email worked well during the same day when it was created, but after the one night's backup routines and "cleaning up" the index was still there but not used when I ran new tests. It seemed to have been "disconnected". I had to delete it and create it ones more to make it work. I will follow up the situation tomorrow but I would be surprised if it will work. Any idea of what I have done wrong?
Regards
Janne

-----Original Message-----
From: WEB400 [mailto:web400-bounces@xxxxxxxxxxxx] On Behalf Of Jim Oberholtzer
Sent: den 1 juli 2015 18:08
To: 'Web Enabling the IBM i (AS/400 and iSeries)'
Subject: Re: [WEB400] WS problems

Janne,

Try looking at the Index advisor and see if the query engine is building new
indexes you did not expect. You might have to clear the index advice
first to get a clearer picture but my first thought was the query engine is
optimizing the query differently. Since this is V6R1 it might be one
query is running with the SQE and the other is running with the CQE. Are
there any derived indexes out there the new process runs?

--
Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects

-----Original Message-----
From: WEB400 [mailto:web400-bounces@xxxxxxxxxxxx] On Behalf Of Janne Lindh
Sent: Wednesday, July 01, 2015 10:23 AM
To: web400@xxxxxxxxxxxx
Subject: [WEB400] WS problems

2015-06-29
I have some problems with a new Web service that runs too slow compared to an older one of the same kind.
The old service retrieves 1000 records from a table and returns 9 xml tags per record within 2-3 seconds, which is ok. This one has been running perfectly for years now.
A new service that we are testing takes about 15 seconds to retrieve only
100 records with 17 xml tags a record. The extra tags are filled by chaining an extra file but I have tested the routine without those extra "gets" with the same negative response time. I also ran tests with direct CALL from a program just to eliminate the xml conversion. In those cases, it took less than 3 seconds to retrieve 300 records in both services.
I also measured the time by writing timestamps into a file at the start and at end of the call just to eliminate the time for transportation of data and conversion in the GUI. That gave the same results as I found in the GUI display.
Both services are part of a Service program written in RPG and use SQL for retrieving the records. The data is returned using an array with maximum
1000 element but only the actual numbers are converted to xml.
The OS version on i5 is 6.1.
Are there any limitations in iseries that make the new service with more element per record work slower? If so is there a way for me to make any changes in the system. Or is the difference in time to find elsewhere?

Med vänlig hälsning

Janne Lindh
Konsult
Janne.lindh@xxxxxxxx

[Beskrivning: Beskrivning: cid:image002.png@01CD05A9.5F7EA680]
evry.com
_________________________

EVRY One Blue AB
Mobil: +46 (0) 70 342 2466

Besöksadress : Ekenbergsvägen 113
Postadress : 171 79 SOLNA

EVRY är resultatet av fusionen mellan EDB, ErgoGroup och SYSteam. EVRY är ett ledande nordiskt IT-företag som kombinerar verksamhetskunnande och IT-kompetens med lokal närhet och flexibilitet.

This message contains information which may be confidential and privileged.
Unless you are the intended recipient (or authorized to receive this message for the intended recipient), you may not use, copy, print, disseminate or disclose to anyone the message, the attachments hereto or any information contained in the message. If you have received the message in error, please advise the sender by reply e-mail, and delete the message with any attachments.

--
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing list To post a message email: WEB400@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/web400.

--
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing list To post a message email: WEB400@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/web400.