Technique #2: partitioning transactions via stored
procedures
Imagine your PowerBuilder client contains the following
code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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:
-
Step 1 — Move the business logic (or as much as possible)
outside of the transaction. In other words, the business logic
should appear either before all Updates of the transaction or
after Commit of the transaction. This way the transaction is not
tied up while the business logic is executing. -
Step 2 — Partition the transaction by moving all the
Updates into a stored procedure. The stored procedure will be
executed on the database side and only return the final result.
This would eliminate the multiple server calls from the multiple
updates to just one server call over the WAN for saving all the
data in one shot.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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 are 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 are equal to better scalability and faster
performance.