Oleg and Readers:

I feel we're off on a tangent here... Yes, there are different ways to do
things in SQLObject.  Yes, efficiency would be better if we changed our code
to only use the most efficient methods. 

I will post another thread highlighting a test case for all this get-by-id
action.

The overall point is performance, and the crucial eager-loading idea is not
in-scope.  Thus, I'm trying to see if I can do one small thing to help, and
that is bound vars.

Two options present themselves: (a) A get-by-id prepare could be run at
table generation time.  Or, (b) having a sqlmeta definition enabling it (or
defining the method name), so a TableA.createBoundGetByID() could be run
at-will by our application.  I think B is better because all our tables
already are generated, but A is an additional option, too.

Failover seems to be a bad idea from the transaction viewpoint and the code
complexity viewpoint.  I'm glad I tried it, to know that it won't work, but
yes, I'm okay with giving up on failover.

The SQLObject._init location does seem like the proper location, though,
since it's a known and likely get-by-id situation.

Questions for everyone:
1.  Is the idea of optional (defined by an sqlmeta) prepared/bound get-by-id
interesting?
2.  Is there agreement that there would be significant performance gain with
it?
3.  Is there a way to implement it that's relatively simple and therefore
reliable?

My answers are Yes to all 3 questions.  I'm interested in all opinions...?

Among the user community, has anyone else turned on SQL debug and watched
all the "select where.. Id=22" stuff goes by and wondered if we could speed
this part up?

-- Kevin

___________________________________
Kevin J. Rice
Senior Software Engineer, Textura LLC
51-K Sherwood Terrace, Lake Bluff IL  
(847) 235-8437 (spells VISAFLUIDS)
(847) 845-7423 (845-RICE, cellphone)
___________________________________


-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Oleg
Broytmann
Sent: Wednesday, August 29, 2007 8:48 AM
To: sqlobject-discuss@lists.sourceforge.net
Subject: Re: [SQLObject] Attempt at Implementing Bound Params inSQLObjectfor
get-by-id calls

On Mon, Aug 27, 2007 at 09:04:06PM -0500, Kevin J. Rice wrote:
> the moment I hit the first
> error, the dbconnection says 'current transaction is aborted, commands 
> ignored until end of transaction block'.

   The message comes from PostgreSQL, not from SQLObject. After you have hit
an error Postgres aborts the transaction and waits until the client closes
the connection.

> > ... to speed up joins try SQL*Joins classes ...
> Yes.  Hmmm.  We've tried this.  Consider:
> 
> ...Presume standard sql*join setup...
> for recA in tableA.select():
>   .. Do stuff with recA
>   for brec in recA.btablerecs:
>       .. do stuff with brec.field1
>   .. Do other stuff with recA
> 
> This generates something like 12 bazillion selects:
> Select record_id_list from tableA
> Select * from tableA where record id = 1 Select record_id_list from 
> tableB where tableb.tableaid = 1 Select * from tableB where record id 
> = 1 Select * from tableB where record id = 2 ... (until done) Select * 
> from tableA where record id = 2 ... (until done) Select * from tableA 
> where record id = 3

   Seems you have tried something different. The following program

class TableA(SQLObject):
   table_b = SQLRelatedJoin('TableB', addRemoveName='B')

class TableB(SQLObject):
   table_a = SQLRelatedJoin('TableA', addRemoveName='A')

TableB.createTable()
TableA.createTable()

b1 = TableB()
b2 = TableB()

a1 = TableA()
a1.addB(b1)
a1.addB(b2)

for b in a1.table_b:
   print b

   produces the following output:

 1/Query   :  CREATE TABLE table_b (
    id INTEGER PRIMARY KEY
)
 1/QueryR  :  CREATE TABLE table_b (
    id INTEGER PRIMARY KEY
)
 2/Query   :  CREATE TABLE table_a (
    id INTEGER PRIMARY KEY
)
 2/QueryR  :  CREATE TABLE table_a (
    id INTEGER PRIMARY KEY
)
 3/Query   :  CREATE TABLE table_a_table_b (
table_a_id INT NOT NULL,
table_b_id INT NOT NULL
)
 3/QueryR  :  CREATE TABLE table_a_table_b ( table_a_id INT NOT NULL,
table_b_id INT NOT NULL
)
 4/QueryIns:  INSERT INTO table_b VALUES (NULL)  4/QueryR  :  INSERT INTO
table_b VALUES (NULL)
 5/QueryOne:  SELECT NULL FROM table_b WHERE id = (1)  5/QueryR  :  SELECT
NULL FROM table_b WHERE id = (1)
 6/QueryIns:  INSERT INTO table_b VALUES (NULL)  6/QueryR  :  INSERT INTO
table_b VALUES (NULL)
 7/QueryOne:  SELECT NULL FROM table_b WHERE id = (2)  7/QueryR  :  SELECT
NULL FROM table_b WHERE id = (2)
 8/QueryIns:  INSERT INTO table_a VALUES (NULL)  8/QueryR  :  INSERT INTO
table_a VALUES (NULL)
 9/QueryOne:  SELECT NULL FROM table_a WHERE id = (1)  9/QueryR  :  SELECT
NULL FROM table_a WHERE id = (1)
10/Query   :  INSERT INTO table_a_table_b (table_a_id, table_b_id) VALUES
(1, 1)
10/QueryR  :  INSERT INTO table_a_table_b (table_a_id, table_b_id) VALUES
(1, 1)
11/Query   :  INSERT INTO table_a_table_b (table_a_id, table_b_id) VALUES
(1, 2)
11/QueryR  :  INSERT INTO table_a_table_b (table_a_id, table_b_id) VALUES
(1, 2) 12/Select  :  SELECT table_b.id FROM table_b, table_a_table_b,
table_a WHERE ((table_b.id = table_a_table_b.table_b_id) AND
((table_a_table_b.table_a_id = table_a.id) AND (table_a.id = 1))) 12/QueryR
:  SELECT table_b.id FROM table_b, table_a_table_b, table_a WHERE
((table_b.id = table_a_table_b.table_b_id) AND ((table_a_table_b.table_a_id
= table_a.id) AND (table_a.id = 1))) <TableB 1 > <TableB 2 >

   Look at the last SELECT - there is one select to draw all objects for
.table_b.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            [EMAIL PROTECTED]
           Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >>  http://get.splunk.com/
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss


-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >>  http://get.splunk.com/
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Reply via email to