Re: [SQLObject] Inefficient MultipleJoin

[Andrey L. <an...@mi...>]

On Sun, Jul 18, 2004 at 01:50:43PM -0500, Charles Brandt wrote:
> Hi Andrey,
> 
> > I must admit this way of doing things is terribly unscalable. Isn't it
> > better to do single "SELECT id, dn, discount, ... FROM customer WHERE
> > pricing = 4"? Or am I doing something wrong?
> >
> 
> I think the behavior that you're observing is in fact the way that SQLObject
> does things.  The reason being if your select were to return thousands of
> rows of data but you only need the first 10, 10 specific calls would be more
> efficient than grabbing all 1000 rows.  (Especially by reusing the same
> connection?)  I'm no database expert though...

I'm no database expert too, but I feel that not always true. Each query
consists of transferring over wires, syntax parsing, planning,
optimizing, locking and unlocking tables, etc.  Fetching records one by
one, generating masses of queries is not a way to go IMHO. Again, I have
no benchmark results whatsoever, just intuition.

> 
> From the docs:
> 
> "Select results are generators, which are lazily evaluated. So the SQL is
> only executed when you iterate over the select results, or if you use list()
> to force the result to be executed. When you iterate over the select
> results, rows are fetched one at a time. This way you can iterate over large
> results without keeping the entire result set in memory. You can also do
> things like .reversed() without fetching and reversing the entire result -- 
> instead, SQLObject can change the SQL that is sent so you get equivalent
> results.
> You can also slice select results. The results are used in the SQL query, so
> peeps[:10] will result in LIMIT 10 being added to the end of the SQL query.
> If the slice cannot be performed in the SQL (e.g., peeps[:-10]), then the
> select is executed, and the slice is performed on the list of results. This
> will only happen when you use negative indexes."

Well, I'm afraid that's not really a case. At least [:n] notion doesn't
work here (doesn't result in LIMIT statement). When I change driver
script to::

    pricings = Pricing.select()

    for prsg in pricings:
        print prsg.id
        print prsg.customers[:10]
 
script still generates lots and lots of "SELECT dn, balance, pricing
FROM customer WHERE id = 160"-like statements.

Join.py line 100:

    def performJoin(self, inst):
        ids = inst._connection._SO_selectJoin(
            self.otherClass,
            self.joinColumn,
            inst.id)
        if inst._SO_perConnection:
            conn = inst._connection
        else:
            conn = None
        return self._applyOrderBy([self.otherClass(id, conn) for (id,) in ids if id is not None], self.otherClass)

I can see no way where LIMIT statement or similar python limiting mechanism is
inserted. I can be wrong here of course.


I'm using SQLObject-0.5.2 and python-3.3.4

I'm currently looking at several object-relational mappers for python
and "usage" part of SQLObject is cleanly the best! But I'm a bit afraid
to base big and somewhat performance dependant project on it... 

BTW, do you aware of any MSSQL Server 2000 connectors for SQLObject?

Thanks


-- 
Andrey Lebedev aka -.- . -.. -.. . .-.
Software engineer at UAB Mikromarketingas (http://micro.lt)
Homepage: http://micro.lt/~andrey/
Jabber ID: ke...@ja...