On Monday 10 August 2015 10:36 PM, Claudio Freire wrote:
On Fri, Aug 7, 2015 at 6:58 PM, kk <krm...@gmail.com> wrote:
On Friday 07 August 2015 10:05 PM, Claudio Freire wrote:
On Fri, Aug 7, 2015 at 12:51 PM, Mike Bayer <mike...@zzzcomputing.com>
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.
It may be a myth most of the time, but there are cases where it is not.
I had one case (in a whole decade of programming, so it is indeed very
rare) in which a very complex query ran very fast, and planning time
was the dominant cost (think 150ms for planning and 15ms for
execution). For that query, preparing it explicitly saved a lot of
runtime. Again, executemany helps when it's implemented with prepared
statements. But I was using psycopg2 and it doesn't use prepared
statements, so I had to prepare them explicitly myself. This was with
SQLAlchemy 0.3 and I managed to do it just fine. Some black magic was
needed of course (had to explicitly compile the query to SQL, bind
parameters, and generate a PREPARE statement from it), but nothing too
complex.
Now, pg8000 does use prepared statements, so it may be as simple as
using that driver if the need for prepared statements is there. You
can even have two engines pointing to the same database and use pg8000
only for the queries that really need prepared statements. So
SQLAlchemy (and DBAPI) has come a long way since I found that case I
mentioned above. I'm sure today handling that case would have been
even easier.
Thanks for the details reply.
Now I understand it better.
So you mean to say executemany will give me the needed performance gain in a
nutshell.
Is that curect?
In a nutshell, yes.
Secondly, is executemany good at only Insert, or Update or both?
Both. More precisely, anything that doesn't produce results.
And lastly if I have a big resultset through a select statement, more so
from a view, what is the best approach to use if I decide not to use stored
procedures?
Well, the bigger your result sets are, the less you care about store procedures.
Assuming store procedures speed up queries (which is a big IF that is
very often false), they only speed up the planning phase, not the
execution. There's no execution optimization you cannot accomplish
with raw SQL, so the bigger the result set, the less you care about
planning time, and thus the less the relative benefit from using
stored procedures is.
Honestly, the benefits of store procedures is so small, and their
maintainance cost so high, that I would suggest never using them
unless you find a case you've thoroughly analyzed and profiled, and
that you find they'd be a huge help (which won't happen for a big
while).
So esssentially baked statements as Mike was pointing seems to be a great
solution for bigger resultsets.
Is that correct?
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.
