Re: RPC, PB5, SYBASE 11 STORED PROCS

I'm surprised there hasn't been more discussion on this topic. Are there any other replies to Tim's reply???

"Tim Azzopardi" <TimAzzopardi_at_holdtern.demon.co.uk> sez:
>
>However I have several reservations about this way of working with
>PB/Sybase...
 

>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)

It sounds like you're comparing apples to oranges in the sense that PowerBuilder itself when using SQL-based DW's does not package up the transaction but will send individual INSERT/UPDATE/DELETE statements. So the RPC method does not seem any worse in this sense; when comparing the RPC calls to PB's normal SQL-based dw behavior, I don't think points (c) through (e) really apply.

Now, comparing the RPC method to the string method directly is another matter. Point (c) is one of those things that must be benchmarked. It may be insignificant, then again it may not be. As for points (d) and (e), sure networks go down and users reset the apps but Sybase generally is pretty good about noticing dead connections and killing them (sometimes you need to tune the kernel properly setting things like KEEP_ALIVE parameters, etc.) so this shouldn't really be a big problem.

>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).

This sounds pretty good but I see one difficulty. Perhaps you've solved it. How do you manage to tell the user which record has failed? Say your packaged string calls 20 update procedures each corresponding to a record in a tabular datawindow. Generally upon a failure, you'll display a message and position the cursor on the record that failed. Maybe you get this information from the error message itself?

>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.

That's about it...

Overall, I like your approach. So, when you call Update(), you let PB cycle through the dw and you build a big long string in the subsequent SQLPreview events? When do you actually send it to the server?

Regards,
Mike Received on Fri Mar 21 1997 - 00:00:00 CET