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. -- 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.
