On 8/7/15 11:05 AM, kk wrote:
On Friday 07 August 2015 03:03 PM, Ladislav Lenart wrote:
Hello.
ORM is certainly slower. How much depends A LOT on your workload. For
example
bulk operations with ORM are an order of magnitude slower than raw
SQL. On the
other hand, SQLAlchemy Core let's you write generative SQL queries
without ORM
features which are as performant as raw SQL.
So is is there some kind of a method to have some prepared sql
statements in SQLAlchemy itself?
I have seen that prepared statements in other languages like Java do a
great job.
That's totally a myth and you can see me ranting on this whole topic of
"explicit prepared statements == SPEED" here:
https://mail.python.org/pipermail/db-sig/2014-December/006147.html See
the benchmark there. Whatever "performance" we get with prepared
statements is vanishingly small and utterly dwarfed by the
order-of-magnitude-greater latencies we get from Python. The DBAPI
already has a great speed optimization in this area and it is known as
executemany() - it applies only to CRUD statements, not SELECT, but
SQLAlchemy uses executemany() very heavily and to great effect - the
speed gains here are not so much due to prepared statements, as psycopg2
does not use them in any way, but due to the fact that we roll up lots
of data into a single call that psycopg2 can run from pure compiled C code.
Reading that thread overall, you'll learn at the very least that the
Python DBAPI does not expose prepared statements. As you'll note,
I'm entirely against the idea of them being made explicit, for this
exact reason; now everyone's going to want the concept expressed
explicitly in SQLAlchemy, involving that multiple resource-holding
cursors be held onto which then open the doors to all kinds of new
concurrency / memory / connection pool / locking issues that will all be
reported as new bugs that I have to worry about, all for absolutely no
good reason as explicit PS does just about nothing to help performance
in any real way. Yet another chronically misunderstood concept that
everyone is going to demand everywhere even if you show them that it's
pointless (see:
http://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-databases/
for the reigning king of this phenomenon).
Now, there is something in SQLAlchemy that will give you an *enormous*
boost of speed that is basically doing what everyone things a "prepared"
statement will do, which is a Python-side "prepare" of everything.
Because compared to the database's time to set up a statement handle,
the time it takes for SQLAlchemy to set up a core Select from a Query
object as well as the time to build the Query itself is very
significant. That feature is known as Baked Queries and it is
documented here:
http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/baked.html. A lot
of work went into this very unique feature and it is also benchmarked in
the example suite, which I would strongly recommend you read and run fully.
Overall SQLAlchemy is an excellent
library to work with!
So you mean performance will really get hit when pure ORM is used.
So shold I use a mixture?
For inserts let's say orm and for bulk select queries some prepared
statement like thing (if it exists )?
The suite in
http://docs.sqlalchemy.org/en/rel_1_0/orm/examples.html#module-examples.performance
was built in order to provide the answers to these questions. That's
where you need to be.
Happy hacking.
Krishnakant.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.