PowerServer 2021 Help
Tuning excessive server calls

Imagine your PowerBuilder client contains the following code:

long ll_rows, i
decimal ldec_price, ldec_qty, ldec_amount

ll_rows = dw_1.retrieve(arg_orderid)
for i = 1 to ll_rows
    dw_1.SetItem(i, "price", dw_1.GetItemDecimal(i, "price")*1.2)
next 

if dw_1.update() < 0 then
        rollback;
        return
end if

for i = 1 to ll_rows
	ldec_price = dw_1.GetItemDecimal(i, "price")
	ldec_qty = dw_1.GetItemDecimal(i, "qty")
	
	if ldec_price >= 100 then
		ldec_amount = ldec_amount + ldec_price*ldec_qty
	end if
Next

ll_rows = dw_2.Retrieve(arg_orderid)
dw_2.SetItem(dw_2.GetRow(), "amount", ldec_amount)

If dw_2.update() = 1 then
	Commit;
else
	rollback;
end if

This is not only problematic from a runtime performance perspective since there would be numerous server calls over the WAN, but also it could result in a "long transaction" that would tie up the database resulting in poor database scalability.

The business logic and the data access logic (for saving data) are intermingled. When the first "Update( )" is submitted to the database, the related table in the database will be locked until the entire transaction is ended by the "Commit( )". The longer a transaction is the longer other clients must wait, resulting in fewer transactions per unit of time.

To improve the performance and scalability of the application, the above code can be partitioned in two steps:

It is generally best to divide the original transaction into three segments or procedures: "Retrieve Data", "Calculate" (time-consuming logic), and "Save Data". The "Retrieve Data" procedure retrieves all required data for the calculation. This data usually would be cached in a DataWindow(s) or a DataStore(s). In the "Calculate" procedure, the data cached in DataStore will be used to perform the calculation instead of retrieving data directly from the database. The calculation result would be cached to the DataStore and then saved to the database by the "Save Data" procedure.

Example of the new PowerBuilder client code partitioned into three segments and invoking a stored procedure to perform the Updates:

long ll_rows, i
decimal ldec_price, ldec_qty, ldec_amount
//Retrieve data
dw_2.Retrieve(arg_orderid)
ll_rows = dw_1.retrieve(arg_orderid)
//Calculate (time-consuming logic)
for i = 1 to ll_rows
    dw_1.SetItem(i, "price", dw_1.GetItemDecimal(i, "price")*1.2)
next

for i = 1 to ll_rows
	ldec_price = dw_1.GetItemDecimal(i, "price")
	ldec_qty = dw_1.GetItemDecimal(i, "qty")
	
	if ldec_price >= 100 then
		ldec_amount = ldec_amount + ldec_price*ldec_qty
	end if
Next

dw_2.SetItem(dw_2.GetRow(), "amount", ldec_amount)
//Save data
declare UpdateOrder procedure for up_UpdateOrder @OrderID = :arg_orderid,
@amount = :ldec_amount;
execute UpdateOrder;

Example of code for the stored procedure to update the database:

create procedure up_UpdateOrder(
@orderid integer, 
@amount decimal(18, 2)
)
as
begin
update order_detail set price = price*1.2
where ordered = @orderid

if @@error <> 0
begin
    rollback
        return dba.uf_raiseerror()
end

update orders set amount = @amount
where ordered = @orderid

if @@error <> 0
begin
    rollback
        return dba.uf_raiseerror()
end

commit
end

In summary, with the above performance optimization technique, the performance and scalability is improved since the transaction is shorter. The server call-inducing Updates are all implemented on the server-side rather than the client-side, improving the response time. Secondly, moving the business logic out of the transaction further shortens the transaction. If the business logic cannot be moved out of the transaction, one may want to consider implementing the business logic together with the transaction as a stored procedure. In summary, shorter transactions equal to better scalability and faster performance.