Thanks a lot Jim.
I used the Navigator but could not find any "advised index" to handle the select and sort order that my RPG program want so I created one index of my own. That did it! 5 seconds to retrieve 1000 records is good enough.
It is strange that the same program runs against different schemas but I had only to change in one of them.
Would you recommend that I clear all advised indexes and let IBMi create new ones? The schema that I created index in has 46 advised indexes while the other one has only 20. Even though they have the same tables. Why the difference?
Greetings
Janne Lindh
-----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.
midrange.com
