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?
Secondly, is executemany good at only Insert, or Update or both?
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?
I will come with more questions before the decision is made so I may
take some more of your valuable time.
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.
