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