Menu
I was looking at the question and decided to try using the bind variables. I use
sql = 'insert into abc2 (interfield,textfield) values (%s,%s)'
a = time.time()
for i in range(10000):
#just a wrapper around cursor.execute
db.executeUpdateCommand(sql,(i,'test'))
db.commit()
and
sql = 'insert into abc2 (intfield,textfield) values (%(x)s,%(y)s)'
for i in range(10000):
db.executeUpdateCommand(sql,{'x':i,'y':'test'})
db.commit()
Looking at the time taken for the two sets, above it seems like there isn't much time difference. In fact, the second one takes longer. Can someone correct me if I've made a mistake somewhere? using psycopg2 here.
837
A bind variable is an Apex variable that you use in a SOQL query. Use bind variables as placeholders for specific values to be provided later. This isn't a new concept. In Object-Oriented Programming for Admins, you learned about using a parameter as a placeholder in a method.
In PostgreSQL, bind variables are numbers preceeded by a $ sign. When using SQL Relay bind functions, to refer to an Oracle, Sybase or MS SQL Server bind variable, you should use its name without the preceeding colon.
By using bind variables exclusively in your code, you avoid concatenating SQL statements and thereby prevent malicious users from altering or injecting additional statements. Oracle database uses the value of the bind variable exclusively and does not interpret its contents in any way.
Bind parameters—also called dynamic parameters or bind variables—are an alternative way to pass data to the database.
As far as I know, psycopg2 has never supported server-side parameter binding ("bind variables" in Oracle parlance). Current versions of PostgreSQL do support it at the protocol level using prepared statements, but only a few connector libraries make use of it. The Postgres wiki notes this here. Here are some connectors that you might want to try: (I haven't used these myself.)
As long as you're using DB-API calls, you probably ought to consider cursor.executemany() instead of repeatedly calling cursor.execute().
Also, binding parameters to their query in the server (instead of in the connector) is not always going to be faster in PostgreSQL. Note this FAQ entry.
159
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
