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