Re: Informix vs. Sybase vs. Oracle vs. (gasp) MS SQL Server

>What does this mean in specifics? Do you do 1 row per page? Do you
>design your files differently? Or is your code written to work around
>this solution?

First, my answers here are only intended to be very general. The simple answer is 'It depends', but realistically the solution to contention issues is in the database schema itself. In other words, the tables were not designed to match and model the business flows, purposes and rules.

One simple example of a poor design would be designing your customer list to have both names and addresses in the same table. When you need to change an address, you end up also locking the name data, whether on a row or the page basis.

The only time I've ever done one row per page was on a very small table that only contained 'next id' values for all tables in the system. This was on Sybase - on Oracle I would not have needed to do it.

Another example:
On a very high activity OLTP database with many users, I tend to keep lots of processing logic out of triggers, since that can be a point of contention in any RDBMS.

I have run into contention issues sometimes in a running system where additional functionality needs to be added. It gets a little trickier here, but frequently it can be handled just by adding another index or restructuring current indexes.

Unfortunately there are no hard and fast rules.

>I appreciate everyone taking the time to discuss this issue, but by
>now all comments have bolied down to either "row locking is better" or
>"no it's not".
>
>Can anyone be more specific about this? What steps can be taken to
>resolve this?

Basically the 'market' has spoken and RLL will be available in all RDBMSs. There are apps written by folk who were either poorly trained or rushed or ? that do not work well with PLL (SAP is the major one IMO) and its being added in by both MS and Sybase.

>Does this issues matter to smaller systems? In rough terms when does
>this become a problem? How many files and how many records must exist
>before you run into the locking vs performance issue?
>
>Let me give one example of how I handled a problem in Foxpro.

Good solution, but IMO the issue may have been created by a design that did not realize ahead of time that you would need to keep an on-the-fly total.

The PLL vs RLL issue only comes up in larger systems where maximum performance is needed. In small systems, the workaround is king and is a workable path. After all, writing code and systems is supposed to benefit the company and its users for a business purpose, not be a perfect implementation.

Gary Received on Sat Dec 06 1997 - 00:00:00 CET