Re: [SQLObject] two postgres questions

[Edmund L. <el...@in...>]

Ian Bicking wrote:

> I think views don't jive well with SQLObject -- they create an opaque
> structure.

Why not have views result in read-only objects? Viz:

class roObject(SQLObject):
     _view = 'my_view'
     ...
     ...


> But yes, normalization can lead to a whole bunch of tables. 
> I'm interested extending the power of joins so that some of these can be
> handled more easily, without having to have too many classes involved.

And this is where every ORM falls down right now. When you're starting 
from scratch, every ORM is more or less OK. But if you have to use it 
with a pre-existing schema, oop...


> But you don't have to use complicated queries anyway.  There's nothing
> intrinsically Wrong about doing the logic in Python.  List comprehension
> even makes set operations look nicer, and caching can provide
> performance improvements.

But this means writing an algorithm instead of describing what you want 
(declaratively, with SQL).

I haven't done any benchmarking, but given all the indexing that can go 
on in an RDBMS, it seems to me that the iterative Python version of:

select *
from table_one
where col_one in (
    select col_two
    from table_two
    where blah)

Is going to be slower since there isn't going to be the benefit of 
indexing. Caching won't make anything faster since the RDBMS is caching too.

OTOH, the overhead of the RDBMS parsing, planning, etc. might be quite 
high. Hmmm... anybody care to speculate on this? I must benchmark this 
one day...


> It could probably be argued that those complicated queries are a form of
> premature optimization.  Like most optimization, complex queries usually
> are based on the requirements of pieces of code that would otherwise be
> considered factored.  Those queries may be an important form of
> optimization, but I think that should still be accessible even if it
> won't be as natural as using normal object access.

I don't know if I agree with you about this... I'll have to think about 
it more. The point about using SQL is that you aren't thinking about 
optimization or algorithms at all. You're thinking of the data and 
relationships embodied therein--or at least you should be.

In a perfect world, you figure out what data you're looking for, then 
describe what you want. It is the RDBMS that does the optimization to 
give you what you describe. It is only when it isn't performing well 
that one would even think of optimizing your SQL, adding more indices, etc.

...Edmund.