Re: [sqlalchemy] Performance issues with multi-shard (engines) sharing the same bakery / LRUCache

On Tue, Nov 26, 2019, at 3:01 AM, Carson Ip wrote: > Hi Mike, > > Thanks for the swift response. > > Then I guess the easiest fix for me is to modify the cache key composition to > replace the dialect object part with a dialect summary (e.g. type + version) > that is the same between all

Re: [sqlalchemy] Performance issues with multi-shard (engines) sharing the same bakery / LRUCache

Hi Mike, Thanks for the swift response. Then I guess the easiest fix for me is to modify the cache key composition to replace the dialect object part with a dialect summary (e.g. type + version) that is the same between all engines. But it requires a guarantee that all compiled SQL queries

Re: [sqlalchemy] Performance issues with multi-shard (engines) sharing the same bakery / LRUCache

On Mon, Nov 25, 2019, at 4:40 AM, Carson Ip wrote: > *Background:* > ** > I am using a multi-shard MySQL setup (multiple db hosts, each host holding > many databases, a.k.a. "shards"). My Python application is creating engines > to many of these shards. For performance reasons, the application

[sqlalchemy] Performance issues with multi-shard (engines) sharing the same bakery / LRUCache

*Background:* I am using a multi-shard MySQL setup (multiple db hosts, each host holding many databases, a.k.a. "shards"). My Python application is creating engines to many of these shards. For performance reasons, the application utilizes bakery and BakedQuery to avoid compiling SQL

Re: [sqlalchemy] Performance issue with selectinload with views and PostgreSQL

On Thu, Sep 20, 2018 at 11:16 AM Jayson Reis wrote: > > Hi there, I guess I will try to change in the next following days. > Do you think a change like this is worth it for SQLAlchemy? big time, if you are seeing the join be the difference between 8 seconds and 100ms. at first I wasnt sure

Re: [sqlalchemy] Performance issue with selectinload with views and PostgreSQL

Hi there, I guess I will try to change in the next following days. Do you think a change like this is worth it for SQLAlchemy? Jayson Reis Em qui, 20 de set de 2018 às 16:31, Mike Bayer escreveu: > for guidance on how the flag is added, look at the "innerjoin=True" > flag on joinedload() /

Re: [sqlalchemy] Performance issue with selectinload with views and PostgreSQL

for guidance on how the flag is added, look at the "innerjoin=True" flag on joinedload() / relationship(). On Thu, Sep 20, 2018 at 10:30 AM Mike Bayer wrote: > > you would need to change the code here: > > https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/strategies.py#L1914 > >

Re: [sqlalchemy] Performance issue with selectinload with views and PostgreSQL

you would need to change the code here: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/strategies.py#L1914 however, you can't just change the query to unconditionally not include the parent table, because you don't know what the "primaryjoin" is.for this to be automatic,

[sqlalchemy] Performance issue with selectinload with views and PostgreSQL

Hi there everyone. I would like to ask if there is a way to change how selectinload run its query. I put an example [1] that will show you sort of how my data is structured so you can have an idea, but the gist is: I have a main table (like customers) and another table (like order) which

Re: [sqlalchemy] Performance of ORDER BY vs. list.sort() vs. sorted()

On Wed, Feb 28, 2018 at 3:02 PM, wrote: > Hello, > > I’m curious about your experience with sorting the results of all() > > queries which return a list. It seems to me that there are

[sqlalchemy] Performance of ORDER BY vs. list.sort() vs. sorted()

Hello, I’m curious about your experience with sorting the results of all() queries which return a list. It seems to me that there are three ways of sorting such result lists: 1. Use an ORDER BY

Re: [sqlalchemy] Performance questions on semi-large result sets

On Wed, Oct 21, 2015 at 11:43 PM, Mike Bayer wrote: > If we OTOH use native_unicode=False and use the Unicode type for the > columns we care about, that seems a lot faster, e.g. changing the > mapping to: [...] > When we use the Unicode type and set

Re: [sqlalchemy] Performance questions on semi-large result sets

On 10/21/15 4:52 PM, Uri Okrent wrote: > Hello, I'm trying to understand the various performance implications of > working with the ORM layer, vs the SQL language layer, vs a raw dbapi > connection. Ideally, I would like to stick with the ORM layer > > First, the set up: > CentOS 6 >

Re: [sqlalchemy] Performance questions on semi-large result sets

On 10/21/15 7:27 PM, Uri Okrent wrote: > Interesting... > > On Wednesday, October 21, 2015 at 5:43:22 PM UTC-4, Michael Bayer wrote: > > class Customer(Base): > __tablename__ = "customer" > id = Column(Integer, primary_key=True) > name = Column(Unicode(255)) >

Re: [sqlalchemy] Performance questions on semi-large result sets

Interesting... On Wednesday, October 21, 2015 at 5:43:22 PM UTC-4, Michael Bayer wrote: > > class Customer(Base): > __tablename__ = "customer" > id = Column(Integer, primary_key=True) > name = Column(Unicode(255)) > description = Column(Unicode(255)) > My declarative classes

Re: [sqlalchemy] performance of SA

On 7/24/15 2:19 PM, Jonathon Nelson wrote: I should preface this by saying I'm a huge fan of SQLAlchemy. Huge! However, when trying to extol the virtues of SQLAlchemy I inevitably run into this issue: But it's slow! My usual response to that is that, yes, it is somewhat slower than raw

Re: [sqlalchemy] performance of SA

On 7/24/15 2:44 PM, Mike Bayer wrote: On 7/24/15 2:19 PM, Jonathon Nelson wrote: I should preface this by saying I'm a huge fan of SQLAlchemy. Huge! However, when trying to extol the virtues of SQLAlchemy I inevitably run into this issue: But it's slow! My usual response to that is

[sqlalchemy] performance of SA

I should preface this by saying I'm a huge fan of SQLAlchemy. Huge! However, when trying to extol the virtues of SQLAlchemy I inevitably run into this issue: But it's slow! My usual response to that is that, yes, it is somewhat slower than raw MySQL or PostgreSQL or whatever DB-API you are

Re: [sqlalchemy] Performance problem of simple orm usage

Dear Jonathan, thank you for your cool remix of recommendations. Very appreciated. On Donnerstag, 26. Juni 2014 15:09:03 Jonathan Vanasco wrote: In case this helps... This reminds me slightly of some RFID work I did years ago. We had a lot of reads coming in from different units, several

Re: [sqlalchemy] Performance problem of simple orm usage

On 6/26/14, 11:49 AM, Hans-Peter Jansen wrote: Dear SQLAchemistas, I'm suffering from a performance problem from a simple sequence like this: rec is a sensor record coming redundantly from the network, enos is a sensor device, ev is a sensor value record: def store_enos_rec(self,

Re: [sqlalchemy] Performance problem of simple orm usage

On 6/26/14, 12:24 PM, Mike Bayer wrote: I'm not sure what kind of application this is but I would not be mixing transactional control, that is the commit/rollback, inside of a business method that only seeks to create some new objects. There'd be a containing pattern within which

Re: [sqlalchemy] Performance problem of simple orm usage

On 6/26/14, 3:07 PM, Hans-Peter Jansen wrote: Okay, attached is a profile dump with .commit() out of the way. Here's the head of it: -- p = pstats.Stats('srelay.pstats') -- p.strip_dirs().sort_stats('cumulative').print_stats(100) Thu Jun 26 20:41:50 2014 srelay.pstats 55993702 function calls

Re: [sqlalchemy] Performance problem of simple orm usage

Dear Mike, sorry for not coping with preferred reply behavior.. On Donnerstag, 26. Juni 2014 15:26:02 Mike Bayer wrote: On 6/26/14, 3:07 PM, Hans-Peter Jansen wrote: Obviously, some operation triggers the flush method with about the same consequences.. OK, turn off autoflush - either

Re: [sqlalchemy] Performance problem of simple orm usage

On 6/26/14, 5:18 PM, Hans-Peter Jansen wrote: Dear Mike, sorry for not coping with preferred reply behavior.. On Donnerstag, 26. Juni 2014 15:26:02 Mike Bayer wrote: On 6/26/14, 3:07 PM, Hans-Peter Jansen wrote: Obviously, some operation triggers the flush method with about the same

Re: [sqlalchemy] Performance problem of simple orm usage

In case this helps... This reminds me slightly of some RFID work I did years ago. We had a lot of reads coming in from different units, several reads per unit per second. I found the best way to handle writing was to just access the db directly, but kept the ORM on the read side. I recall a

[sqlalchemy] YCombinator startup seeks SQLAlchemy performance contract engineer

Hey guys, I'm Amir - Founder of Toutpost. We're a YCombinator 2013 company. We're growing very fast but are having performance issues. We have never scaled an application on SQLAlchemy before so we think that our usage of relationships is suboptimal. It's preventing us from implementing a

Re: [sqlalchemy] Performance degradation after few hours running

On Dec 12, 2012, at 9:33 PM, Jaimy Azle wrote: Previously, I use SQLA 0.7.2 on Jython for production and it works great for almost one year deployment until recently I manage to upgrade to 0.7.8. With this new version, I noticed a constant performance degradation mostly after serveral hours

Re: [sqlalchemy] Performance degradation after few hours running

On Thursday, December 13, 2012, 10:26:33 PM, Michael Bayer wrote: Almost nothing changed when we were upgrading SQLA except for Jython which should be upgraded from 2.5.2 to 2.5.3 to fix SQLA import problem. Other technology stack used: Also assuming Jython 2.5.3 + SQLA 0.7.2 is fine, right?

[sqlalchemy] Performance degradation after few hours running

Previously, I use SQLA 0.7.2 on Jython for production and it works great for almost one year deployment until recently I manage to upgrade to 0.7.8. With this new version, I noticed a constant performance degradation mostly after serveral hours running, eventually overall system become almost

Re: [sqlalchemy] Performance Mystery

does this occur with a raw mysqlconnect script ? if so, how about a MySQLdb version of the same thing ? start looking at the DBAPI. On Jul 25, 2012, at 3:45 AM, Warwick Prince wrote: I have taken the SQL entered into the MySQL Workbench (see below) and run that same SQL directly as a

[sqlalchemy] Performance Mystery

Hi All This is addressed to anyone who may be able to shed some light on this strange behaviour; I'm running MySQL on Windows 2003 Server (Sorry) and have a table that has ~2M rows of sales data in it. Knowing the type of BI queries I would want to be doing, I have added indexes where I

Re: [sqlalchemy] Performance Mystery

One difference that exists here is the usage of bound parameters, which I'm assuming you're not using when you run the query in the console. What happens if you run a test script with straight mysqlconnector ? Try with and without the bound parameters. On Jul 24, 2012, at 11:50 PM,

Re: [sqlalchemy] performance vs. psycopg2

On Fri, Dec 16, 2011 at 15:58, Jon Nelson jnel...@jamponi.net wrote: Revised to use: for row in rows:  dict(row) # throw away result  count += 1 I wonder how this could even work... iterating over the row yields individual values, not tuples?! I wonder what kind of column types you are

Re: [sqlalchemy] performance vs. psycopg2

On Thu, Dec 15, 2011 at 19:52, Jon Nelson jnel...@jamponi.net wrote: On Thu, Dec 15, 2011 at 12:01 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Dec 15, 2011, at 12:51 PM, Jon Nelson wrote: Up front, I'm not using the ORM at all, and I'm using SQLAlchemy 0.7.4 with psycopg2 2.4.3 on

Re: [sqlalchemy] performance vs. psycopg2

On Fri, Dec 16, 2011 at 3:30 AM, Gaëtan de Menten gdemen...@gmail.com wrote: On Thu, Dec 15, 2011 at 19:52, Jon Nelson jnel...@jamponi.net wrote: On Thu, Dec 15, 2011 at 12:01 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Dec 15, 2011, at 12:51 PM, Jon Nelson wrote: Up front, I'm not

[sqlalchemy] performance vs. psycopg2

Up front, I'm not using the ORM at all, and I'm using SQLAlchemy 0.7.4 with psycopg2 2.4.3 on PostgreSQL 8.4.10 on Linux x86_64. I did some performance testing. Selecting 75 million rows (a straight up SELECT colA from tableA) from a 5GB table yielded some interesting results. psycopg2 averaged

Re: [sqlalchemy] performance vs. psycopg2

On Dec 15, 2011, at 12:51 PM, Jon Nelson wrote: Up front, I'm not using the ORM at all, and I'm using SQLAlchemy 0.7.4 with psycopg2 2.4.3 on PostgreSQL 8.4.10 on Linux x86_64. I did some performance testing. Selecting 75 million rows (a straight up SELECT colA from tableA) from a 5GB

Re: [sqlalchemy] performance vs. psycopg2

On Dec 15, 2011, at 1:01 PM, Michael Bayer wrote: haven't clocked it but a source inspection indicates Python's would be much slower, as it's going for much more correct and comprehensive behavior using a linked list. Here's our __iter__() (self._list is a native Python list):

Re: [sqlalchemy] performance vs. psycopg2

On Thu, Dec 15, 2011 at 12:01 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Dec 15, 2011, at 12:51 PM, Jon Nelson wrote: Up front, I'm not using the ORM at all, and I'm using SQLAlchemy 0.7.4 with psycopg2 2.4.3 on PostgreSQL 8.4.10 on Linux x86_64. I did some performance testing.

[sqlalchemy] Performance: orm vs sql

The little diddly below is comparing performance of orm access vs sql expression language. When I run it with number=1 I get a 5.8x advantage for sql. When I run it 10 times I get a 2.7x advantage. The actual numbers are, respectively: 1.47375132 0.25630808 5.45569524 1.96911144 Is this a

Re: [sqlalchemy] Performance: orm vs sql

On Aug 18, 2010, at 4:16 PM, Michael Hipp wrote: The little diddly below is comparing performance of orm access vs sql expression language. When I run it with number=1 I get a 5.8x advantage for sql. When I run it 10 times I get a 2.7x advantage. The actual numbers are, respectively:

Re: [sqlalchemy] Performance Difference

Paulo Aquino wrote: Are there performance difference between the following line of codes. a. for instance in Query: print instance.id b. for instance in Query.all(): print instance.id b. is equivalent to list(query). Differences are negligible since the full results are

[sqlalchemy] Performance Difference

Are there performance difference between the following line of codes. a. for instance in Query: print instance.id b. for instance in Query.all(): print instance.id -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group,

[sqlalchemy] performance issues

Hello, I've got some scripts which take quite a bit of time and I wanted to investigate why they were so long. So I profiled them using cProfile and I was quite surprised to get the following results. In short, SQL queries themselves take less than 10% of the total time, and most of the time is

Re: [sqlalchemy] performance issues

On Dec 15, 2009, at 16:30 , Antoine Pitrou wrote: I've got some scripts which take quite a bit of time and I wanted to investigate why they were so long. (..) I should point out that this particular script doesn't manipulate that many objects (a few thousands of them perhaps), and the

Re: [sqlalchemy] performance issues

Antoine Pitrou wrote: Hello, I've got some scripts which take quite a bit of time and I wanted to investigate why they were so long. So I profiled them using cProfile and I was quite surprised to get the following results. In short, SQL queries themselves take less than 10% of the total

Re: [sqlalchemy] performance issues

On Dec 15, 2009, at 11:47 AM, Michael Bayer wrote: the 53K calls to mapper._save_obj() indicate that your flush plan is creating an enormous dependency tree. Or, much less likely, you're using the batch=False flag on mapper, which is highly not recommended. For a simple flush plan,

Re: [sqlalchemy] performance issues

On Dec 15, 2009, at 6:09 PM, Michael Bayer wrote: On Dec 15, 2009, at 11:47 AM, Michael Bayer wrote: the 53K calls to mapper._save_obj() indicate that your flush plan is creating an enormous dependency tree. Or, much less likely, you're using the batch=False flag on mapper, which

[sqlalchemy] Performance Analysis

Hi all, This is not really a sqlalchemy question but I was wondering if anyone developing with sqlalchemy knows the answer. Turning on echo=True spits out all the queries that the application generates. Currently I am directing this output to a file and then looking through to derive statistics

[sqlalchemy] Performance, cd

Hello everyone, I nailed the problem with performance, it wasn't the 'big query' not loading collections, but this one: rsvs = session.query(Reservation).filter(Reservation.project_id.in_(projids)).filter(Reservation.status == 'pending').filter(Reservation.end_date todaydt).all() The

[sqlalchemy] Performance

Hello everyone, Basically, I have some performance issues. Here's a somewhat complicated story: I have a Host class with several relations to OS_family, OS_version, OS_release, Project, Reservation, etc. The entire (sanitized) SQL log is too big to be posted here, so I copied it into

[sqlalchemy] Performance problem related to saving newly created objects.

Here is the scenario. I'm using SA as the data abstraction/access layer between a desktop application and postgresql database. The user interacts with the application primarily by browsing large numbers of records (on the order of tens of thousands of records at once). Occasionally the users

[sqlalchemy] Performance problem with Psyco

Hi. I'm using Python 2.5.1 + SQLAlchemy + Psyco 1.6 for accelerating Python computations. Everything works ok for SQLAchemy 0.4.4 (50% performance boost), but for SQLAlchemy 0.4.5, 0.4.6 and latest SVN version (0_4 branch) there is a huge slowdown and profiling shows that SA's code takes much

[sqlalchemy] Performance of data mapping and plain access

Hi all, while trying the ORM side of sqlalchemy, I noticed a remarkable speed difference between using the ORM machinery and the plain db access. The attached test consists of 1000 inserts and 1000 selects; its purpose is just to test the overhead of the data mapping system. The ORM version