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
