Re: S.O.D.A. database Query API - call for comments

In comp.databases Tobias Brox <tobiasb_at_suptra.org> wrote:
: A table row is just a table row. It's not useful for anything than
: making the database work. So I see no use of encapsulating a table row
: inside an object. That would certainly be messy. A table row should
: never be encapsulated at all, that make searching more difficult. The
: whole idea about encapsulation does not fit in a database context.
 

: An object, at the other hand, is a construct that simulates the real
: world, and a construct that it's easy to deal with in a programming
: environment. Storing an object in tables is not a difficult task.

*sigh*.. You must have missed that day in class.

A table row is an instance of an Entity. An entity is often a real world object. Sometimes it is a concept, sometimes an action, always something you might model as an object. The only difference are some constraints on table complexity, and the strong binding between objects and methods in objects; and the latter difference is still in question.

What does 'not useful for anything than making the database work' even mean, anyway? Are disks and idexes also therefore not useful?

:> How about storing objects 
:> as column values in table rows. Then you can keep a declarative query
:> language and be able to call methods on columns. 
 

: Hm ... having one complete object as a single attribute in a table row?
: Must be a reference in that case? Or just a piece of binary data?
 

: I'm not sure if that's a good idea. For one thing, the data within the
: object will be encapsulated, searching will be difficult. For the other
: thing, I think it might make sense to call methods on a table row. I.e.
: you have some geometrical data about a box in a table row, but not the
: volume. The volume can be returned through a method.

We like to call that a function, or a stored procedure according to your flavor.

: I'm not quite sure, but I think I share your opinion that the query
: language ideally should be declarative. I think a lot of problems might
: be solved by using complex views. Particularly if it was possible to
: insert, update and delete rows in a view.

You already *can* perform DML on a view.

: There is just one big catch, it's extremely difficult to make really
: good query optimizers. Try doing something really fancy with SQL, and
: the very most database servers will stall for ages before returning
: something. At my former workplace, I was even adviced that for a simple
: join over two tables, I should take out all data (90% unuseful data)
: from both tables and rather use perl to filter out the data I needed.

Your co-workers were fools. I do not say this to insult, only to be honest. I have run into this myth several times in my career, and it is always hard to convince beginning programmers otherwise. The biggest performance hits in a database are scanning/reading the disk, and sending the data over the network or IPC. Fully specifying your query allows the DB to read as little as possible off the disk, which speeds it up in the primary, and reduces the amount sent over the wire, which speeds it up in the secondary. Thirdly, the client application doesn't have to manipulate huge structures in memory trying to filter down to the set they wanted in the 1st place! Try to understand- the DB does not sit there, spinning its wheels trying to figure out how to query, while chewing up as much processor time as SETI -- there aren't that many possible search paths.

You should also be concered about bugs -- your former coworkers supposed that you or they can correctly implement SQL selection and sorting faster and better than people who have worked on it for 20 years.

Don't believe me? Put 10,000,000 rows into several tables in a database (a very moderate number). Add indexes on several columns. Then:

1. read the entire database into the client program, sort it and filter it yourself, then operate on and display the resulting data.
2. actually learn good SQL, write detailed queries, operate on and display the precise set.

Which runs faster? Received on Sat Jul 21 2001 - 20:01:21 CEST