tirkarthi commented on issue #31340: URL: https://github.com/apache/airflow/issues/31340#issuecomment-1555003042
i find this odd and maybe I am missing something here but changing the lazy loading method to select gives noticeable improvement. Maybe changing permissions to use "select" might give a similar improvement. As per sqlalchemy docs select would result in a query per access that looks inefficient to selectin/joined since roles are displayed in the page that should trigger a query per entry, It was initially select and was later changed to joined and then to selectin in below commits. https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html ### Changed to selectin commit d7d2b49f29af007ff7f386a405cd1ba882a1818e Author: Ash Berlin-Taylor <[email protected]> Date: Fri Apr 8 22:24:26 2022 +0100 Speed up `has_access` decorator by ~200ms (#22858) Using the ORM to create all the Role, Permission, Action and Resource objects, only to throw them all away _on every request_ is slow. And since we are now using the API more and more in the UI it's starting to get noticeable This changes the `user.perm` property to issue a custom query that returns the tuple of action_name, permission_name we want, bypassing the ORM object inflation entirely, and since `user.roles` isn't needed in most requests we no longer eagerly load that. * Fix tests Caching issues that only crop up in tests (but not ever a problem in the request life cycle of webserver ### Changed to joined commit 3e9828022b03b60d9e112f1f64340a528c8407e3 Author: James Timmins <[email protected]> Date: Fri Feb 4 09:09:52 2022 -0800 ```patch diff --git a/airflow/www/fab_security/sqla/models.py b/airflow/www/fab_security/sqla/models.py index 5c83588801..08faecdb25 100644 --- a/airflow/www/fab_security/sqla/models.py +++ b/airflow/www/fab_security/sqla/models.py @@ -155,7 +155,7 @@ class User(Model): last_login = Column(DateTime) login_count = Column(Integer) fail_login_count = Column(Integer) - roles = relationship("Role", secondary=assoc_user_role, backref="user", lazy="selectin") + roles = relationship("Role", secondary=assoc_user_role, backref="user", lazy="select") created_on = Column(DateTime, default=datetime.datetime.now, nullable=True) changed_on = Column(DateTime, default=datetime.datetime.now, nullable=True) ``` ``` In [1]: from airflow.utils.session import create_session In [2]: from airflow.www.fab_security.sqla.models import User, Role # With lazy="selectin" In [5]: %timeit with create_session() as session: session.query(User).all() 77.1 ms ± 8.09 ms per loop (mean ± std. dev. of 7 runs, 10 loops each) # With lazy="select" In [4]: %timeit with create_session() as session: session.query(User).all() 3.25 ms ± 49.4 µs per loop (mean ± std. dev. of 7 runs, 100 loops each) ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
