Re: RPC, PB5, SYBASE 11 STORED PROCS

From: Tim Azzopardi <TimAzzopardi_at_holdtern.demon.co.uk>
Date: 1997/03/15
Message-ID: <01bc30cb$978ce960$0100007f_at_aaa>

A very nice article by somebody who really understands Sybase and PowerBuilder (for a change).

I think that this represents a very good way of working in most cases. It is certainly better that the standard DECLARE method of calling procs via the SQLPreview event.

However I have several reservations about this way of working with PB/Sybase...

The first is entirely related to PowerBuilder the product and probably not that important with a top end PC. If I have 2000 stored procedures in my application (far from uncommon) I have some very big inhherited transaction objects beacause of the number of defined RPC. This will add processing time because (a) the objects need to be loaded to use and (b) powerbuilder uses dyamic linking. This may cancel out the processing gain of not constructing strings. I don't know becasue I have never tried this method.

The others reservations are more fundamental to client/server communication in general.

Lets say that dw_1.Update calls one RPC to update a master record and dw_2.Update() calls twenty RPCs to update child records.

Problem (c) There are 23 bidirectional communications across the network including the begin tran, commit tran. These must be processed by the client and the server. The number of bidirectional communications is dependant on the number of calls to be generated. For several records there will be an overhead avoided by packaging it all up in one string and sending it in one go.

Problem (d) Suppose the database gets hit hard and slows right down halfway through, by other users running reports/dba doing online backups/lock conflicts. The user thinks the client app has hung and kills it or switches her machine off and on. (This is what happens in the real world). This results in an uncommited transaction holding locks for as long as it takes sybase/the dba to kill it with a loss of all the updates.

Problem (e) The network goes down. The result is the same as for (d)

What this all boils down to is that the duration of the global transaction is partly dependant and extended by network transfer time. In addition the network transfer time is proportional to the number of procedure calls.

My preferred approach is to package the entire transaction in one string and send it in one hit to the server.

This fully problems eliminates problems

(c) because 1 string is send to the server, thus there is one two way
communication with the server.independant of the number of proc calls.

	and


	(d) if the user gets bored with waiting and kills the app, the entire

transaction has been sent and will eventually be commited. and either vastly or completely reduces problem

(e). The network may go down while some of the multiple network packets
holding the string arrive at the server but the timeframe in which this can happen is greatly reduced. (I'm not sure whether the server can start processing the string as soon as it receives the first packet. If it can't then problem (e) is eliminated).

One could argue that the similar problems to (a) and (b) will occur in the contruction of a single string transaction, with bloated SQLPreview event code. With a simplistic approach this would be the case. However an experienced designer will contruct classes (user objects in PowerSpeak) with one or two methods that generate the stored procedure calls (+ associated error handling).

The kind of code you might end up with is

dw_1.Update(TRUE, FALSE) // No database access, just string construction via SQL Preview
dw_2.Update(TRUE, FALSE) // No database access, just string construction via SQL Preview

SQLServer.ExecuteInTransaction (dw_1.tsql + dw_2.tsql) // Send the string in one hit to the server.

IF sqlca.SQLCode <> 0 THEN

     // Transaction allready rolled back
     // Save error info in:  sqlca.SQLDBCode and sqlca.SQLErrText
     // Display error info saved above after rollback is done.
     Return

END IF dw_1.ResetUpdate()
dw_2.ResetUpdate()

The SQLServer.ExecuteInTransaction method perfoms the following tasks

o	Wrap the string in  transaction handling code
o	Execute it (via dynamic sql format 4) - YES you can send ANY arbritary
lenght valid transact sql NOT just one proc call.
o	Obtain any results returned in selects either in or between the proc

calls. (Such as return codes, ids and timestamps)

The use of RPCs as you correctly say saves the server string processing time. However does one also assume that the time taken to parse the strings is significant compared to the time taken to process the procedure? I don't believe this to be the case for non trivial industrial strenght applications.

There are additional more detailed problems with the multiple c/s communication transaction model you have described. For example what if dw_1.Update () creates a record where the ID is calculated/assigned by the proc, and the procs called by dw_2.Update() need to send this ID to create child records. This involves more client side processing between dw_1.Update() and dw_2.Update(). I feel this further justifies the single string approach because one can also code (in T-SQL) this transfer of arguments into the string, send it to the server and then forget about it.

The only practical problem I know with the single string approach is that PowerBuilder strings are limited to 32/64K but I have yet to find an application where this is an issue.

Our two views on the subject both have their pros and cons and I hope I have demonstrated the validity of mine. I would welcome any feedback from you or anybody else. Received on Sat Mar 15 1997 - 00:00:00 CET