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

Johan Andersson wrote:

> So, *are you listening Anthony*, I propose the following test application,
> with an ever increasing transaction complexity, to show that the finer
> granularity of lock the better performance, _for this type of application_.

	From your description below it looks more like a variable transaction
	rate rather than variable transaction complexity, but this is just a
	minor quibble.

> Consider a traffic monitoring system, be it trucks, trains, cars, airplanes or
> whatever. Lets assume we have an extremely simple data model:

        It could be anything that changes state.
>
> create table vehicle(
> id integer not null primary key,
> lat char(10),
> long char(10),
> ltime date year to fraction
> );

	You could try defining 'lat' and 'long' as smaller numeric
	datatypes as opposed to char(10). This is to allow more rows
	to be packed onto a page.

> Assume that we have two types of 'users'. One feeder and one reader.
>
> The feeders:
> The feeders could be regional traffic 'followers', transponder signal
> collectors, radars or whatever. The feeders send, as one transaction, a
> collection of tuples to the database containing the current situation. Each
> tuple has: (id, lat, long, observationtime, NewFlag, RemoveFlag)
>
> The feeder transaction will therefore be to:
> begin work
> update all vehicles for tuples with NewFlag,RemoveFlag == FALSE
> delete all vehicles for tuples with RemoveFlag = TRUE
> insert new vehicles for tuples with NewFlag = TRUE
> commit work

	You could get away with one flag here (i.e. 1 = New, 0 = Delete,
	Null = Update), but this is just more nitpicking.

> NOTE!
> It is not acceptable to use one transaction per tuple received, all or none
> must be registred.

	Now this is a sticking point! Why? Assuming we are monitoring
	traffic flow, information wold be coming in from more than one source
	and it would be in real time. I suspect that it should really be
	atomic. I.e.

	update this vehicle
	delete this vehicle
	insert this vehicle

	etc. Just a relatively constant flow. I wouldn't expect there to
	be a rollback of all the data if a deadlock arises. Recommiting
	the transaction could then possibly increase the contention further.

> The readers are typically operators that are responsible for a given
> geographical area.
>
> The reader transaction will be therefore be a simple select from the vehicle
> table restricted by coordinates, ie:
> select * from vehicle
> where lat > x1 and lat < x2 and long > x3 and long < x4
>
> Now there are three essential variables to manipulate and two variables to
> measure.
>
> M1 - The number of feeders
> M2 - The average number of tuples sent from one feeder as one transaction.
> M3 - The number of readers
>
> V1 - The average response time for a reader select.
> V2 - The average throughput time, ie the time it takes from a feeder sends
> a tuple until it can be fetched by a reader.
>
> It is important to keep the average number of rows fetched by reader constant.

	Why? I think it should be all rows that match the selection criteria.
	This will vary constantly and can't and shouldn't be controlled.

> It is assumed that the feeders are sending new transactions repeatedly with a
> constant delay, small relative to transaction time, in between.

        The delay shouldn't be constant either. It should appear to be realtime.

> My assumption is that when the feeder transactions start to get longer in real
> time, both response time and throughput time will decrease more on a system
> having only page locks than on a system having row locks.

	Its possible. Contention would increase as the number of feeders
	and readers increase and the size of the data sets they handle
	increase. Whether you get a decrease in performance depends.
	There's only one real sure way to tell.

> This seems obvious to me because when one feeder deletes or updates a vehicle,
> all vehicles on that page will be locked for both(?) other feeders and all
> readers until the transaction is committed. The risk of deadlocks among
> feeders in a PLL system is also an issue worth watching out for.

	You shouldn't get deadlocks with just the one table. From the design,
	it appears that only one index is required. More than likely you
	would just get blocking so it would appear to an end user as stop/
	start performance.

-am Received on Fri Dec 12 1997 - 00:00:00 CET