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

Johan Andersson wrote:
>
> no_sp.am_at_agd.nsw.gov.au says...
> >
> >Johan Andersson wrote:
> >>
> >> > And increasing concurrency gives you ... ?
> >> Faster response times to the users, for example.
> > And not increased contention for resources as well?
> >
> Of course, which is why I assume that having as fine a granularity as possible
> when locking resources would decrease response times.

	I think we'll never get real closure on this issue without devising
	some real mechanism to test it. I'll make a proposal below. Whether
	it gets picked up on is another matter. As for th above issue - I
	took the weekend break to do some research, both thru my library
	and at some of my regular bookstores. I went looking for some
	Informix books (and one in particular, which I couldn't find),
	there weren't that many. Neither were there many Sybase books.
	Oracle books seemed to dominate the shelf space - the majority
	being from Oracle Press itself (I won't comment on this but others
	can draw their own conclusions).

	About the best I could find for Informix was "Informix Performance
	Tuning by Elizabeth Suto, Informix Press/Prentice Hall, 2nd Ed.,
	ISBN 0-13-239237-2". To be honest I found the book to be rather
	lightweight for a performance and tuning guide. The only information
	relevant to this debate was in section 8.6 "Using Locks Efficiently"
	(pgs. 120-121), where she says the expected things, keep locks
	short, don't lock too much data, and use RLL to increase concurrency.
	Unfortunately she didn't explain or elaborate on her points. The
	first two points I've cited myself, as have many others.

	From my library I can refer interested parties to "Sybase
	Architecture and Administration by John Kirkwood, Ellis Horwood,
	ISBN 0-13-100330-5, 1993" and "Sybase Performance Tuning by
	Shaibal Roy and Marc B. Sugiyama, Prentice Hall, ISBN 0-13-442997-4,
	1996". Kirkwood's book is rather dated now (he has a new book out
	which appears to be a rewrite entitled "Official Sybase Internals"),
	but still useful. Section 5.3.5 (Ch. 5, pg. 174) of Kirkwood's
	book touches on RLL and PLL with regards to recovery and mentions
	problems with RLL and recovery (this may no longer be an issue).
	He goes into more detail about locking in chapter 6 where he
	covers multi-user considerations (concurrency etc.). There's far
	too much to quote, but he examines the ANSI locking model in detail,
	and gives enough detail on short transactions (section 6.5.2).
	There's probably enough detail in this chapter to flame both
	sides of the debate.

	Roy and Sugiyama's book, while more current, doesn't help any further.
	Section 5.2 onwards (pgs. 108-117) cover the basics. Section 10.4
	onwards (pgs. 355-366) going into further detail. I'm sure anyone
	who looks up these references can pick their own quotes to support
	their own views (I've seen quite a few, but avoided giving actual
	quotes to distill any claims of bias).

	But what I was actually trying to do was not so much look for comments
	on locking and granularity (which support your above comment to
	some extent), but look for comments on the functioning of lock
	managers. Someone posted a description and compared to UNIX
	filesystem locking. While I didn't find anything of use, about
	the only two things I can state is that lock management inevitably
	varies from database vendor to vendor (so generic descriptions
	aren't of much use) and database server lock management is far
	more sophisticated than anything used by a UNIX operating system.
	This being because locking data in a table locks not only a
	row/page/table but also the table's indexes and locking across
	table joins compounds this. Inplicit UNIX locking wouldn't do
	much more than file/directory/inode table.
	


> >> >> The only thing the TPC-C proves is that there exists at least one


> >> >> application for which RLL / PLL is not an issue.

> >> >

> >> My point was that Pablo has no proof of his statement. I have not tried to

> >> prove my assumptions. [...] He waved the

	I'm not so sure. I had hoped to stay impartial on TPC (what a hope!)
	so I didn't cite any of the information I had. However, in Appendix
	F of Roy and Sugiyama's Book there is a discussion of the TPC
benchmarks.
	I'd suggest reading it. They even cite Walter Baker of Informix near
	the bottom of page 579. But TPC-C may seem somewhat dated. TPC-D may
	prove to be a better test (then again it may not). I was interested
	from the point of view of your remarks concerning TPC-C and how long
	it took to actually design TPC-C. There are quite a few issues here.
	TPC is the only benchmarking system available for databases currently.
	I was thinking of suggesting that an alternative be created. However,
	this would be problematic at best. It would take quite some time to
	design suitable and acceptable tests. From earlier comments it also
	takes time to actually set them up and run them. Then there is
	the issue of scrutiny and accountability. The one problem that
	probably annoys me the most is the variability of the hardware
	at the TPC site. As you pointed out in another post, it hardly
	makes for a fair comparison. However, this might make for an
	avenue to explore for interested parties.

> As soon as you introduce an iteration or a recursive algorithm which depends
> on user activity, the complexity becomes unknown at compile time. Even the
> maximum complexity becomes unknown. The most simple example that comes to mind
> is for the user to input an integer number and the application then inserting
> that number of rows in a table, very unrealistic example :-), but it shows
> that transaction complexity need not be decided at compile time.

	True. Its the old determinism issue. However, iterating a transaction
	isn't the same as increasing the complexity of the transactions
	themselves.



>          OK, and my point has been that any application that mandates RLL


>          can be modified so as not to require it. I didn't find any of the

>          cited examples adequate. I believe I suggested alternatives for

>          at least two of them.

	I think we'll just end up with more of the same. This whole thread
	was so much flame bait anyway. I think the next time someone spams
	the newsgroups with a crosspost like this one I'll either tell them
	to go do their own legwork for a change or do what I did about a
	year ago and compare the words "Oracle, Sybase, Informix, etc.".
	Ask a stupid question and all that.

-am Received on Tue Dec 09 1997 - 00:00:00 CET