Thanks for the good advice.
Peter
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of D*B
Sent: Thursday, 30 May 2013 6:20 p.m.
To: Midrange Systems Technical Discussion
Subject: Re: Performance question and SQL SERVER
<snip>
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.
</snip>
first question would be: which of the statements take most of the time in case one?
second question would be:how man time are these statements in case 2?
Both questions could be analyzed by database monitor (toys like VE and OoopsNerv don't help, when you need tools). (STRDBMON for your job before with all details first execution and let it collect data for some executions, ENDDBMON and analyze the data with OoopsNerv)
Some hints:
The query optimizer takes only milliseconds!
If opening a datapath takes more than miliseconds, a temporary index is created and making this index persistent solves this part of the problem.
Open/close operations are lazy closes and take microseconds.
EVIs are often recommended by index advisor, but don't help in most cases.
For complex queries working with a tempfile to extract data befor joining the data is an adequate approach for large tables (some hundreds of millions of records!) and performs better than building complex views, regardless wether you are using CTEs, or persistent views sitting on views.
If you are on V7R1 IBM might have a software defect problem and you a resulting PTF problem.
last recommendation: repeat measuring performance after each step of enhancement and if you don't have any improvement go back to the last stage of implementation.
Moving it to SQL Server would be the very last approach (simpler is better) and if you need this in many jobs, I would recommend ArdGate, it has the smaller runtime footprint.
D*B
--
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
midrange.com
