I was asked to look at a fairly simple SQR program that reported on payroll data. It pivoted data for each employee on which it reported. It makes 21 calls to two procedures that each obtain a value by summing data across different sets of data in one of two payroll result tables.
The ASH data shows that most of the database time, 184 out of 192 seconds is spent in the two statements that aggregate that data. These statements are in the procedures that are called within the driving query.
However, more significantly, only 192 seconds out of a total elapsed run time of 615 seconds is spent in the database. That is just 31%. So most of the time is spent executing code within the SQR program.
We need to look at the code to see exactly what is happening.
This is the driving query. It returns each employee who is paid in a specified payroll, and then for each row the procedure get_gp_acum is used to fetch the sum of certain accumulators for that payroll and employee
This is one of the two procedures that is called to obtain each value. It simply sums the data for that employee.
This code is very clear, well structured, and easy to maintain. The only trouble is that it is slow. Each SQL calls makes SQR do a lot of work, and that takes time.
In this case there was not much procedural code in the SQR and so I was able to coalesce the SQL from the called procedures into the driving query.
If the called procedures had been simple single row look-ups I could have used an outer-join. However, as they are using a group function (sum), I put the query into a scalar query (a query within in the select clause that returns only one row and one column). Each call to a procedure was replaced with a separate scalar query. I ended up with 21 scalar queries.
During this rewrite I encounted an SQR quirk; if the scalar query was placed in the main select clause, SQR produces errors because it is expecting an expression, and it complains that the SELECT keyword is not a variable. I then had to wrap the query in an in-line view. Each scalar query must be given a column alias, and the column alias can be referenced in the SQR select clause.
The SQL looks much more complicated, as does the execution plan. However, the effect on performance was dramatic.
The response of just the combined SQL at 117 seconds is better than the separate SQLs at 154 seconds. Much more significantly, the amount of time spent in SQR (rather than the database) has fallen from 432 seconds to just 37. Therefore, 90% of the SQR response time was spent on submitting the SQL calls in the called procedures.
SQL calls in SQR are expensive. The cost of making lots of calls inside a loop or another driving query can add up to a significant amount of time. SQRs that consume time in this way will also be consuming CPU and memory on the server where the Process Scheduler is located.
In this case, combining SQL statements also improved SQL performance, but that will not always be the case.
There are times when better performance can be achieved at the cost of more convoluted code. In each case there is a judgement to be made as to whether improvement in performance is worth the increase in complexity.