Thread: [SQLObject] Attempt at Implementing Bound Params in SQLObject for get-by-id calls
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Kevin J. R. <kr...@te...> - 2007-08-24 21:12:13
|
Hello! Longtime Lurker here. =20 I've been playing around with the idea that maybe SQLObject could use = bound parameters ("prepared statements") for some types of queries. It seems that we generate a whole lot of queries that are really get-by-id's, especially when we're iterating through a join. Although = it's been mentioned before that caching the result of a join would be a cool thing, I just decided to tackle the simple case of reducing the load on = the DB engine of parsing all those getByID calls by making them calls to = execute a prepared statement. Important point #1: PREPARED STATEMENTS EXECTUTE SIGNIFICANTLY FASTER = than parsed ones, especially when our standard query by id passes all the = column names in to make sure we know the order in which they're coming back, = which is a lot of text to parse, analyze, plan, and execute. Although some SQL's are different in this regard, let's pretend for a = moment that we don't have to worry about cross engine compatibility and think = in Postgres mode; if this doesn't work in other engines, surely we can = disable it and use the existing retrieval in that case. But, many other engines = do 'prepare's, so this might actually be portable. Ok, so In Postgres, the prepare statement works as follows (example from http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html): PREPARE nameOfPreparedStatement (int, date) AS SELECT * FROM users u, logs l=20 WHERE u.usrid=3D$1 AND u.usrid=3Dl.usrid AND l.date =3D $2; EXECUTE nameOfPreparedStatement(1, current_date); Please bear in mind I've not delved into SQLObject internals very much, = so forgive the newbie errors. Further PLEASE forgive the wordy debugging statements I've left in the below code since they might help someone thinking about this problem further and/or testing it out. OVERKILL REMOVED: I had put in this nice idea of creating a 'signature' = for each prepared query in the form of concat'ing the tablename with a list = of the params as a string and md5'ing the sucker to create a name for it = (have to do something; Postgres has a 64 char limit to a var name). Then, I yanked it because I figured that if the query dies due to bad column = names, the logic below will automatically re-prepare the statement and try it again. Regardless, it's an alternative to the simpler "sqlobject_getbyid_tablename" naming convention. So, after some looking and puzzling over the obfuscated meaning of selectResults and other minutiae, I thought the best place for this is = in sqlobject/main.py, about line 950, in SQLObject class's _init() method. Here's the code. It works for some trivial cases but not others = (joins?), and I've not figured that part out yet. But, I would like to kindly = SOLICIT ADVICE if this is a good idea, if this is doomed, if this has been tried before, and other general opinions from the crowd before any attempt to proceed further, and hope that any advice would come with specific suggestions=85=20 Pseudocode: - come up with the prep'd statement's name,=20 - try to execute it; - if that doesn't work: - destroy anything by that name,=20 - try to recreate it,=20 - try to run it again, and=20 - if that doesn't work: - failover to the old way. Current bug (can't get past) is that _SO_foreignKey is calling get(), = get() is dying complaining that the id field contains a tuple of row data = instead of an int. ALSO: It occurs to me I might be able to generalize this stuff by = putting the below code into sqlobject/dbconnection.py's = DBAPI._SO_selectOneAlt(), and just prepare all statements there. Don=92t know, suggestions = welcome! Enjoy! #------------------------------------------- code start --------------------- def _init(self, id, connection=3DNone, selectResults=3DNone): assert id is not None # This function gets called only when the object is # created, unlike __init__ which would be called # anytime the object was returned from cache. self.id =3D id self._SO_writeLock =3D threading.Lock() # If no connection was given, we'll inherit the class # instance variable which should have a _connection # attribute. if connection is not None: self._connection =3D connection # Sometimes we need to know if this instance is # global or tied to a particular connection. # This flag tells us that: self.sqlmeta._perConnection =3D True if not selectResults: #print "*" * 1000 import sys, traceback from pprint import pprint, pformat selectResults =3D None prepName =3D '' if type(self.id) =3D=3D type(5): try: try: columnList =3D self.sqlmeta.columnList cols =3D ','.join([x.dbName for x in self.sqlmeta.columnList]) print "cols =3D ", cols prepName =3D 'sqlobject_getByID_%s' % str(self.__class__.__name__)=20 executeString =3D "execute %s(%s);" % (prepName, = self.id) print "Initial ExecuteString: %s"% (executeString) selectResults =3D = self._connection.queryAll(executeString) except: msg =3D apply(traceback.format_exception, = sys.exc_info()) print "Execute-Level Traceback: %s" % (msg) try: deallocString =3D "deallocate %s;" % (prepName) #print "Deallocate string: %s" % (deallocString) selectResults =3D self._connection.query(deallocString) except: msg =3D apply(traceback.format_exception, sys.exc_info()) print "Deallocate-Level Traceback: = (string=3D%s): %s" % (deallocString, msg) pass try: prepString =3D 'Prepare %s (int) as select %s = from %s where %s.%s =3D $1;' % ( prepName, cols, self.__class__.__name__, self.__class__.__name__, self.sqlmeta.idName) print "Prepstring =3D %s" % (prepString) selectResults =3D = self._connection.query(prepString) except: msg =3D apply(traceback.format_exception, sys.exc_info()) print "Prepare-Level Traceback: %s" % (msg) pass try: executeString =3D "execute %s(%s);" % (prepName, self.id) print "Failover ExecuteString: %s"% = (executeString) selectResults =3D self._connection.queryAll(executeString) print "select results:", pformat(selectResults) except: msg =3D apply(traceback.format_exception, sys.exc_info()) print "Failover Execute-Level Traceback: %s" % = (msg) pass =20 except: msg =3D apply(traceback.format_exception, = sys.exc_info()) print "Unknown-Level Traceback: %s" % (msg) pass dbNames =3D [col.dbName for col in self.sqlmeta.columnList] if not selectResults: selectResults =3D self._connection._SO_selectOne(self, = dbNames) if not selectResults: raise SQLObjectNotFound, "The object %s by the ID %s does = not exist" % (self.__class__.__name__, self.id) self._SO_selectInit(selectResults) self._SO_createValues =3D {} self.dirty =3D False -- Kevin ___________________________________ Kevin J. Rice Senior Software Engineer, Textura LLC 51-K Sherwood Terrace, Lake Bluff IL =20 (847) 235-8437 (spells VISAFLUIDS) (847) 845-7423 (845-RICE, cellphone) ___________________________________ |
From: Oleg B. <ph...@ph...> - 2007-08-24 22:24:56
|
Hello! On Fri, Aug 24, 2007 at 04:12:03PM -0500, Kevin J. Rice wrote: > I've been playing around with the idea that maybe SQLObject could use bound > parameters ("prepared statements") for some types of queries. I had tried to do this in the "paramstyles" private branch; look at _executeRetry(), _insertSQL() and _SO_update() at http://svn.colorstudy.com/home/phd/SQLObject/paramstyles/sqlobject/dbconnection.py Also look at http://svn.colorstudy.com/home/phd/SQLObject/paramstyles/sqlobject/include/DBSingleStyle.py The problems that prevented me to merge all this into SQLObject is that different DB API drivers have many problems converting different types. I don't remember exactly, but I think PySQLite has problems with datetime, psycopg with mxDateTime (or vice versa). I haven't tried to retest these drivers (even less other drivers, especially MySQLdb), so I don't know now, they could do better conversions. Now I have a bigger problem because the code in the trunk has changed so much I couldn't just do "svn merge" - I have to merge things manually. PS. This brief answer is not exactly an answer for the question you have asked, and this is intentional. It is 2 past midnight here now, and I am leaving the town for a weekend. I'm answering to let you know your voice is heard. Meanwhile you can read the code, and we will continue the discussion after I return. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2007-08-27 16:21:37
|
Ok, I am back, let's continue... On Fri, Aug 24, 2007 at 04:12:03PM -0500, Kevin J. Rice wrote: > Important point #1: PREPARED STATEMENTS EXECTUTE SIGNIFICANTLY FASTER than > parsed ones Yes, prepared statements by itself should be faster, but you are going to change SQLObject._init() which is used in all creation and retrieval operations. And the change is rather big: > Pseudocode: > - come up with the prep'd statement's name, > - try to execute it; > - if that doesn't work: > - destroy anything by that name, > - try to recreate it, > - try to run it again, and > - if that doesn't work: > - failover to the old way. Are you sure SQLObject in general will not suffer significant performance decrease? > Oleg, I'm confused by your mentioning converting different types in > different databases. With the code in my private branch I have tried to solve much more generic problem - to make all SELECT/INSERT/UPDATE/DELETE statements to use DB API bound parameters: cursor.execute("SELECT * FROM atable WHERE id=?", id) That's a different goal from using PREPARE/EXECUTE. > The issue I'm concerned with on different databases is the fact that > some might have a "prepare" syntax that's different from others. But, > I'll worry about other databases once there is a working prototype. That difference have to be processed in concrete connection classes like PostgresConnection; see how LIMIT/OFFSET and other backend-specific issues are encapsulated in the connection classes. And final note - if your aim is only to speed up joins try SQL*Joins classes (SQLMultipleJoin, SQLRelatedJoin) - instead of iterating over "SELECT id FROM join" they construct a proper SelectResults which is faster (one query for a join) and more correct (orderBy is implemented in SQL instead of Python). Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Kevin J. R. <kr...@te...> - 2007-08-28 02:04:15
|
Oleg and readers: I'm glad you're interested in this idea, even if you're finding potential problems. It has a lot of potential! >> PREPARED STATEMENTS EXECTUTE SIGNIFICANTLY FASTER than parsed ones > Yes, prepared statements by itself should be faster, but you are > going to change SQLObject._init() which is used in all creation > and retrieval operations. And the change is rather big... Yes, agreed. I'm also increasingly thinking this approach won't work (the multiply-nested try/except one). This seems to be because the moment I hit the first error, the dbconnection says 'current transaction is aborted, commands ignored until end of transaction block'. So, if I try and fail, I've toasted my transaction. Yuck. I could be wrong about this behavior, but it looks as if that's what is happening. I could change the code to only call the prepared statements I know exist, then I'll always succeed, and I failover to the old way. I'm working on figuring out how to find out from Postgres if a prepared statement exists. It's not clearly documented, and the #postgres chat is not in this case all-knowing. Alas. BACK TO THE PROBLEM: We are doing a LOT of selects on ID's, which is really stressing our db. I want to reiterate that I have a very limited goal: Increase speed of get-by-id select statements by using prepare. IDEA #2 FOR PREPARE: I've just been playing with sqlbuilder.py, changing the return value of __sqlrepr__ to be 'execute prepStatementForThisTable(idval)' if there's only one table (no joins). This would seeminly work in the very simple case. But, then it would require creating and running one prepare statement per table. I'm thinking the best time for that would be every time our AppServer starts up, that is, as a method call I could make: class dumbTable(SQLObject): class sqlmeta: idName = 'dumbTableID' table = 'dumbTable' firstField = NumericCol(dbName='amount') def __init__(self): self.createPreppedGetByIDStatement() #... Make a call to Sqlobject's __init__()?? def createPreppedGetByIDStatement(self): return '''prepare SQLObject_dumbTable_getByID (int) as select firstField from dumbTable where dumbTableID = $1''' def executePreppedGetByIDStatement(self, id): return '''execute SQLObject_dumbTable_getByID (id)''' Ideally, this could be done automatically in SQLObject's __init__() and I wouldn't have to define the methods in my class. I could just set a sqlmeta 'getByIDprepname', then call something like 'd = dumbTable; d.createPreppedIDStatement()' at the module level so when the .py file is parsed it loads it. >From then on, any call that iterates over the table getting a row at a time by id (as all the joins do) could use this execute. > cursor.execute("SELECT * FROM atable WHERE id=?", id) This might be a wonderful idea, I just don't know. Is this close to done? What are the performance impacts? > ... 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 ... SQLObject is not fixing this kind of thing, it's just the way it works. So, I'm living with that. I'd just like to speed up the get by ids. I've checked, and SQLite, MySQL, and Postgres all support prepare, with seemingly the same syntax (unless there's something subtle I'm missing). So, the cross platform issue isn't, which is a good thing. -- 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: sql...@li... [mailto:sql...@li...] On Behalf Of Oleg Broytmann Sent: Monday, August 27, 2007 11:22 AM To: sql...@li... Subject: Re: [SQLObject] Attempt at Implementing Bound Params in SQLObjectfor get-by-id calls Ok, I am back, let's continue... On Fri, Aug 24, 2007 at 04:12:03PM -0500, Kevin J. Rice wrote: > Important point #1: PREPARED STATEMENTS EXECTUTE SIGNIFICANTLY FASTER > than parsed ones Yes, prepared statements by itself should be faster, but you are going to change SQLObject._init() which is used in all creation and retrieval operations. And the change is rather big: > Pseudocode: > - come up with the prep'd statement's name, > - try to execute it; > - if that doesn't work: > - destroy anything by that name, > - try to recreate it, > - try to run it again, and > - if that doesn't work: > - failover to the old way. Are you sure SQLObject in general will not suffer significant performance decrease? > Oleg, I'm confused by your mentioning converting different types in > different databases. With the code in my private branch I have tried to solve much more generic problem - to make all SELECT/INSERT/UPDATE/DELETE statements to use DB API bound parameters: cursor.execute("SELECT * FROM atable WHERE id=?", id) That's a different goal from using PREPARE/EXECUTE. > The issue I'm concerned with on different databases is the fact that > some might have a "prepare" syntax that's different from others. But, > I'll worry about other databases once there is a working prototype. That difference have to be processed in concrete connection classes like PostgresConnection; see how LIMIT/OFFSET and other backend-specific issues are encapsulated in the connection classes. And final note - if your aim is only to speed up joins try SQL*Joins classes (SQLMultipleJoin, SQLRelatedJoin) - instead of iterating over "SELECT id FROM join" they construct a proper SelectResults which is faster (one query for a join) and more correct (orderBy is implemented in SQL instead of Python). Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... 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 sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Oleg B. <ph...@ph...> - 2007-08-29 13:47:14
|
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/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Kevin J. R. <kr...@te...> - 2007-08-30 19:49:35
|
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: sql...@li... [mailto:sql...@li...] On Behalf Of Oleg Broytmann Sent: Wednesday, August 29, 2007 8:48 AM To: sql...@li... 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/ ph...@ph... 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 sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Oleg B. <ph...@ph...> - 2007-08-30 19:59:07
|
On Thu, Aug 30, 2007 at 02:49:26PM -0500, Kevin J. Rice wrote: > Two options present themselves: (a) A get-by-id prepare could be run at > table generation time. You cannot do that. http://www.postgresql.org/docs/8.2/static/sql-prepare.html "Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use." > 2. Is there agreement that there would be significant performance gain with > it? There could be no agreement. But you might show us there is a gain. > 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? Yes, I had asked the question myself. And answered it by using SQL*Joins ;) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Kevin J. R. <kr...@te...> - 2007-08-30 20:54:58
|
Okay, some revisions to the idea but we're getting somewhere, which is cool. > http://www.postgresql.org/docs/8.2/static/sql-prepare.html Oleg! Mahlohdetz! Good catch on the table generation time with postgres limitation! I guess we're stuck with a non-automated version of this, which requires (building on the previous ideas): class DumbTable(SQLObject): class sqlmeta: idName = 'dumbTableID' table = 'dumbTable' firstField = NumericCol(dbName='amount') def __init__(self): self.createPreppedGetByIDStatement() #... Make a call to Sqlobject's __init__()?? def createPreppedGetByIDStatement(self): return '''prepare SQLObject_dumbTable_getByID (int) as select firstField from dumbTable where dumbTableID = $1''' def executePreppedGetByIDStatement(self, id): return '''execute SQLObject_dumbTable_getByID (id)''' t = createTransaction() # throw away first call to DumbTable, just populate this # transaction/connection with a prepared getbyid. Now, use it: dt = DumbTable(connection=t).createPreppedGetByIDStatement() # now do whatever, presume OtherTable has a foreign # key reference to dumb table id = 2 ff = otherTable.dumbTable.firstField # This ff assignment should generate a call to # dt.executePreppedGetByIDStatement(2) if it exists, and # otherwise do it the old fashioned way. Catch my drift? Is this silly? I'm thinking that if there's low overhead on calling back to the object's executePrepped...() method, then there's no downside to making these calls often, but it could be lots faster in the common case of a non-cached join. -- 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: sql...@li... [mailto:sql...@li...] On Behalf Of Oleg Broytmann Sent: Thursday, August 30, 2007 2:59 PM To: sql...@li... Subject: Re: [SQLObject] Attempt at Implementing Bound Params inSQLObjectforget-by-id calls On Thu, Aug 30, 2007 at 02:49:26PM -0500, Kevin J. Rice wrote: > Two options present themselves: (a) A get-by-id prepare could be run > at table generation time. You cannot do that. http://www.postgresql.org/docs/8.2/static/sql-prepare.html "Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use." > 2. Is there agreement that there would be significant performance > gain with it? There could be no agreement. But you might show us there is a gain. > 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? Yes, I had asked the question myself. And answered it by using SQL*Joins ;) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... 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 sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Oleg B. <ph...@ph...> - 2007-08-30 21:08:25
|
On Thu, Aug 30, 2007 at 03:54:47PM -0500, Kevin J. Rice wrote: > Oleg! Mahlohdetz! (-: > class DumbTable(SQLObject): > def __init__(self): > self.createPreppedGetByIDStatement() > #... Make a call to Sqlobject's __init__()?? Yes, the overridden __init__() must call SQLObject.__init__(). But you have to understand that the __init__() is called on creation of an every Table instance - i.e. on creation (INSERT or SELECT) of an every row. And you probably want to run all PREPARE statements once for a table, not for a row. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |