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
