Re: Performance question and SQL SERVER

I am confused by Dieter's comment - first he calls VE and OoopsNerv toys (ah - he means Ops Nav - OY!), then he says to use one of them to analyze the results of the database monitor. I just checked some other posts to see why he dislikes these so much - one can go looking.

In one post, Dieter suggests just taking the results of the debug messages. As Chuck has pointed out recently, those are useful, but they are just a hint at what is going on. Often a sufficient hint, but in my experience, the VE stuff can be really helpful.

Having said that, these suggestions are worth looking at, too. And as in all things SQL, they are suggestions - we have to try them, then assess, then adjust and try again. Sometimes an index is used to exclude options for the optimizer, and the results may not change.

As an example, I've seen the advice be to create an index that was created as a temp index - then it still builds the temp - one should not always take the advice. That's why requests to have the system create the indexes advised has not flown.

I do agree that creating a temp file can help a lot - going directly to large tables repeatedly can be slower than extracting data of interest, then querying against that.

People like Mike Cain and Kent Milligan insist that with good statement design - that DOES matter, like the join order - and indexes, the performance using SQL should match that of native RLA. I think the jury is out, but I am willing to work for that result.

HTH
Vern

On 5/30/2013 1:20 AM, D*B wrote:

<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