Re: Performance question and SQL SERVER -- MIDRANGE-L

On 5/29/2013 4:02 PM, Matt Olson wrote:

3. Why are you making a work file? Is the SQL Server equivalent creating work files as well? Because if you have to delete/create these perhaps that's where the extra time is being consumed?

I think this is the place I'd start.

Oddly enough, you may find that you get screaming performance over the
full file rather than a work file. That's because of several things:
1) The database manager gets to accumulate meaningful statistics over
the full file. The work file's statistics are transient.
2) You might be able to create appropriate indexes over the full files.
Given those, the optimiser can make some very smart decisions.

--buck

-----Original Message-----
From: Peter Connell [mailto:Peter.Connell@xxxxxxxxxx]
Sent: Wednesday, May 29, 2013 2:48 PM
To: Midrange Systems Technical Discussion
Subject: RE: Performance question and SQL SERVER

I've have an SQL script that runs considerably faster on SQL Server that than on system i.

I have 300 SQL statements which must be run each time a transaction is requested by the client and the response is slow. It is problematic to convert these 300 to native RPGLE which I know would perform well.
The client makes a usual inquiry against a consumer database and a report is generated from matching detail entries on several database files. The report was developed long ago with RPGLE.
But now the RPGLE creates a work file of a few hundred values deemed as significant which are derived from the same data source. The work file is repopulated for each client transaction and its content depends on each new client request, so it differs each time.

Each of the 300 SQL statements (which in fact are supplied by a 3rd party) defines a separate business characteristic that uses SQL to join the work file entries to a predefined table of attributes and return a summary result value.
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.
Diagnostics from running in debug mode show that a repeated request reuses ODPs. Unfortunately, when a new work file is created for the next client transaction, these ODPs get deleted and the transaction is slow again.

I've tried playing around with activation groups , shared opens via OVRDBF and using an running a pre-requisite OPNDBF but had no success in reducing the transaction time.
I am perplexed by the fact that we have a developer familiar with SQL server who has demonstrated that he can easily create a script that runs all 300 statements on SQL server in just a few second with no performance problems. Why is that?

Peter