Hi,
I have gone through the documentation for baked queries.
It sounds interesting and I guess the performance boost is surely going
to be worth noticing.
I just wished to ask one thing, if there are series of queries firing
with each one of them bringing back bulk recordsets, will this concept
still be helpful?
I am asking because then the most important bottleneck wil be the time
the RDBMS takes to parse, compile and execute these queries.
So it kind of becomes a task in itself.
I am not a big ORM expert and don't really know the details under the
hood so asking this.
Happy hacking.
Krishnakant.
On Friday 07 August 2015 09:21 PM, Mike Bayer wrote:
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.
