Re: DBAPI Paramstyle
Tim Roberts wrote: In theory, using a paramstyle allows the query to be sent to the SQL database backend and compiled like a program. Then, successive uses of the same query can be done by sending just the parameters, instead of sending the entire query string to be parsed and compiled again and again. This is commonly done with large production databases like SQL Server and Oracle. For a complicated query, it can be a significant time savings. However, to the best of my knowledge, none of the Python dbabi implementations actually do that. mx.ODBC does, since it is an ODBC implementation. I would be very surprised if the Oracle adapter did not. MySQLdb does not yet, but probably will by the end of summer (with MySQL-4.1 or newer). -- http://mail.python.org/mailman/listinfo/python-list
Re: DBAPI Paramstyle
On Mon, Mar 28, 2005 at 01:43:28PM -0800, Andy Dustman wrote: Tim Roberts wrote: [prepared statements] mx.ODBC does, since it is an ODBC implementation. I would be very surprised if the Oracle adapter did not. MySQLdb does not yet, but probably will by the end of summer (with MySQL-4.1 or newer). pysqlite 2.0 (alpha) does this (compiled statements) too, but currently only for .executemany(). -- Gerhard signature.asc Description: Digital signature -- http://mail.python.org/mailman/listinfo/python-list
Re: DBAPI Paramstyle
Bob Parnes [EMAIL PROTECTED] wrote: I have a mediocre talent at programming, which is why I chose python. For me it was a good choice. I note this so that I hope you understand why I say that I don't know what you are driving at. My understanding is that a paramstyle is more efficient than the traditional python approach for repeated use of a query. If so, then I do not see how the choice of a parameter is relevant. If it is more efficient only in a specific abstract case, then one would have to look for other reasons to use it in a practical application. In theory, using a paramstyle allows the query to be sent to the SQL database backend and compiled like a program. Then, successive uses of the same query can be done by sending just the parameters, instead of sending the entire query string to be parsed and compiled again and again. This is commonly done with large production databases like SQL Server and Oracle. For a complicated query, it can be a significant time savings. However, to the best of my knowledge, none of the Python dbabi implementations actually do that. So, the primary benefit of the paramstyle method is that the database provider inserts whatever quoting is required; you don't have to remember to put single quotes around the arguments, and protect single quotes within the arguments by doubling them, and so on. -- - Tim Roberts, [EMAIL PROTECTED] Providenza Boekelheide, Inc. -- http://mail.python.org/mailman/listinfo/python-list
Re: DBAPI Paramstyle
Tim Roberts wrote: In theory, using a paramstyle allows the query to be sent to the SQL database backend and compiled like a program. Then, successive uses of the same query can be done by sending just the parameters, instead of sending the entire query string to be parsed and compiled again and again. This is commonly done with large production databases like SQL Server and Oracle. For a complicated query, it can be a significant time savings. However, to the best of my knowledge, none of the Python dbabi implementations actually do that. kinterbasdb does. -- http://mail.python.org/mailman/listinfo/python-list
Re: DBAPI Paramstyle
Tim Roberts wrote: In theory, using a paramstyle allows the query to be sent to the SQL database backend and compiled like a program. Then, successive uses of the same query can be done by sending just the parameters, instead of sending the entire query string to be parsed and compiled again and again. This is commonly done with large production databases like SQL Server and Oracle. For a complicated query, it can be a significant time savings. Actually, the slow part is building a query execution plan (deciding which indices to use, join orders, and such). Identifying what is being asked for is trivially fast (compared to any I/O). The DB optimizer works with estimates of running time for many of the possible query plans, and chooses the cheapest of those -- that combinatorial problem is how a DB can chew up CPU time (or save it in some cases). However, to the best of my knowledge, none of the Python dbabi implementations actually do that. This cacheing need not occur anywhere before the DB. There are database systems (DB2 is one) that cache plans by the text of the query. If your query is an exact match with a previously optimized plan (and it hasn't been chucked out of the cache), the plan is retrieved. So, the primary benefit of the paramstyle method is that the database provider inserts whatever quoting is required; you don't have to remember to put single quotes around the arguments, and protect single quotes within the arguments by doubling them, and so on. This _is_ a benefit. Another benefit (greater in my estimation) ls that you are separating code and data to make a more readable (and malleable) query. If things get slow and database access is at fault, you can carry your SQL to a DB expert (sometimes one on site) who can check it to help speed up your system. The DB expert is an SQL dweeb, and will appreciate seeing the SQL done in a straightforward manner. She or he may be able to rewrite the query to improve your access. Perhaps several related queries can be effectively combined (but again, you are talking about an expert in SQL/DB -- they'll need to see all the queries easily in order to help. Perhaps the data access pattern will suggest a change in DB indices (in which case the database administrator can speed up your program without your changing any of your code). You could also be changing the format used to send the data to the database (of that I'm not quite as sure). --Scott David Daniels [EMAIL PROTECTED] -- http://mail.python.org/mailman/listinfo/python-list
Re: DBAPI Paramstyle
On Thu, 24 Mar 2005 15:03:13 +0100, Fredrik Lundh [EMAIL PROTECTED] \ wrote: Bob Parnes wrote: I must be missing something, so perhaps someone can explain the benefit of a paramstyle over the usual Python formatting style and maybe suggest a test to show it. Thanks. set the parameter to 0; DROP DATABASE template1; and see what happens. or set it to os.urandom(1000) and run your test a couple of times to see what happens. Thanks for the suggestion. My system does not appear to contain an os.urandom() method. It has a /dev/urandom device, but I don't know how to use it for this purpose, except perhaps to select the first byte that it produces. I have a mediocre talent at programming, which is why I chose python. For me it was a good choice. I note this so that I hope you understand why I say that I don't know what you are driving at. My understanding is that a paramstyle is more efficient than the traditional python approach for repeated use of a query. If so, then I do not see how the choice of a parameter is relevant. If it is more efficient only in a specific abstract case, then one would have to look for other reasons to use it in a practical application. Bob Parnes -- Bob Parnes [EMAIL PROTECTED] -- http://mail.python.org/mailman/listinfo/python-list
DBAPI Paramstyle
The following script is a one person's comparison of three methods for
accessing a postgresql database using psycopg on a debian computer
running python2.3. Following it are the results of running it six
times.
===
from time import time, clock
import psycopg
MAX_COUNT = 5
def pyMethod():
for n in range(MAX_COUNT):
curs.execute('''SELECT %s;''' % n)
def formatMethod():
for n in range(MAX_COUNT):
curs.execute('''SELECT %s;''', [n])
def pyformatMethod():
for n in range(MAX_COUNT):
curs.execute('''SELECT %(n)s;''', {'n':n})
conn = psycopg.connect(host='localhost', database='template1')
curs = conn.cursor()
for method, func in (('Python method: %f, %f', pyMethod),
('Format method: %f, %f', formatMethod),
('Pyformat method: %f, %f', pyformatMethod)):
startTime = time()
startClock = clock()
func()
print method % ((time() - startTime), (clock() - startClock))
===
[EMAIL PROTECTED]:~/demo$ ./pyformatTst.py
Python method: 9.288770, 3.55000
Format method: 9.457663, 3.82
Pyformat method: 9.446390, 3.70
[EMAIL PROTECTED]:~/demo$ ./pyformatTst.py
Python method: 9.152173, 3.40
Format method: 9.314743, 3.76
Pyformat method: 9.329343, 3.84
[EMAIL PROTECTED]:~/demo$ ./pyformatTst.py
Python method: 9.262013, 3.49
Format method: 9.344197, 3.57
Pyformat method: 9.402157, 3.50
[EMAIL PROTECTED]:~/demo$ ./pyformatTst.py
Python method: 9.170817, 3.86
Format method: 9.509313, 3.26
Pyformat method: 9.380756, 3.77
[EMAIL PROTECTED]:~/demo$ ./pyformatTst.py
Python method: 9.271831, 3.54
Format method: 9.375170, 3.65
Pyformat method: 9.426898, 3.78
[EMAIL PROTECTED]:~/demo$ ./pyformatTst.py
Python method: 9.192097, 3.72
Format method: 9.244554, 3.69
Pyformat method: 9.368582, 3.76
Similar results occurred with an actual database table.
I must be missing something, so perhaps someone can explain
the benefit of a paramstyle over the usual Python formatting
style and maybe suggest a test to show it. Thanks.
Bob Parnes
--
Bob Parnes
[EMAIL PROTECTED]
--
http://mail.python.org/mailman/listinfo/python-list
Re: DBAPI Paramstyle
Bob Parnes wrote: I must be missing something, so perhaps someone can explain the benefit of a paramstyle over the usual Python formatting style and maybe suggest a test to show it. Thanks. set the parameter to 0; DROP DATABASE template1; and see what happens. or set it to os.urandom(1000) and run your test a couple of times to see what happens. /F -- http://mail.python.org/mailman/listinfo/python-list
