On Mon, Sep 28, 2015 at 1:04 AM, David Allouche <da...@allouche.net> wrote:
> On 28 Sep 2015, at 05:21, George V. Reilly <george.v.rei...@gmail.com> > wrote: > > SQLTap is a very useful library that helps you in profiling SQLAlchemy > queries. It helps you understand where and when SQLAlchemy issues queries, > how often they are issued, how many rows are returned, and if you are > issuing queries with duplicate parameters. The last two are new in > tonight's 0.3.10 release, which incorporates improved reporting from me. > > https://github.com/inconshreveable/sqltap > > > What are the downsides of issuing queries with duplicate parameters? Aside > from "it makes it harder to read the logs". > > I mean, obviously, not "dozens of duplicate parameters": any query with > dozens of parameters probably has other problems. > > I could not find any rationale on the linked page. > I was investigating the performance of a single, pathologically slow API request to one of our web services. SQLTap told me that there were nearly 12,000 database queries over several minutes. Digging further, I found that about half of these requests could be eliminated by adding lazy="joined" to some relationships. There was one relationship where adding lazy="joined" eliminated some queries but the result rowsets were so large that the overall API request became noticeably slower. I updated SQLTap's report to show the number of rows returned by each query, which helped identify such problems. I also found that certain objects were being requested again and again; i.e., I was issuing queries with identical parameters. SQLAlchemy issues each query to the database, gets back an identical result (since they weren't being modified), and returns the existing object from its identity map. See http://docs.sqlalchemy.org/en/rel_1_0/orm/session_basics.html#is-the-session-a-cache. Making my app smarter about not asking again for objects that it had already loaded eliminated thousands more queries. This is why I added the duplicate query reporting, so that I could pinpoint opportunities for caching. /George -- 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.
