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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Oleg Broytmann Sent: Monday, August 27, 2007 11:22 AM To: sqlobject-discuss@lists.sourceforge.net 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/ [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