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

Chris Curvey wrote:
>
> > If you have row level locking, the first select stmt will lock one row.
> > As the primary key is 4 bytes you can potentially put 4-500 keys on any
> > page (if you have 2K pages). In a page lock system (like Sybase and
> > MSSQL) the probability of a user trying to access one of the ~499 key
> > values another user has locked is pretty high.
> >
>
> That depends. If you have a small number of master records,
> then the probability of a lock collision is high, but then you *can*
> extend
> the key to be 1K, since you don't need that much disk. If you have
> a large number of master records, then you can control the probability
> of a lock collision via a clustered index.
>
> > Page lock workarounds could be:
> >
> > 1) Make the primary key >1Kb, so you would only have one key per page.
> > (Disks are cheap, right?)

IMHO wasted space (i.e., filler fields) is more of a problem even if disks are cheap. The bigger issue is cache management. If every page/block could hold 4 records and is forced to hold only 1 now, you need 4 times as much cache space. In addition, records are significantly less likely to be in cache as a result of somebody else's SQL statements. Ultimately, you end up with more physical versus logical I/O or a require more memory to maintain the same level of performance.

Kyle Hachey Received on Wed Nov 26 1997 - 00:00:00 CET