hey there - that query is not a cartesian product. the child and child_parent tables are linked together in the WHERE clause. I would assume the query here is in fact taking about half a second to run, the problem here is in the composition of Python objects for every row.
I am assuming that by your notation, "300.000", that's the European "comma" and you are referring to 300K objects. 300000 objects is *enormous* and will absolutely take 25 seconds to load that many full ORM objects because Python is very slow at constructing new objects, not to mention 300K would use tens if not hundreds of megs of memory to store all at once. There's not really any reason you'd ever want that many ORM objects in memory at once and if this is the number of rows you're dealing with, you will need to find a more efficient way of processing them in the way you need, and there are many options depending on what you want to do, whether that be applying some modification, or displaying them in some format, etc. There's a lot written about performance at https://docs.sqlalchemy.org/en/13/faq/performance.html and example suite at https://docs.sqlalchemy.org/en/13/orm/examples.html#module-examples.performance but overall 300000 ORM objects loaded into memory at once will never be efficient, there are many other ways to deal with 300K rows depending on what it is you need to do. Options include pagination for display, batching for updates, dynamic relationships for partial views, working with paginated / non-ORM rows for bulk transfer operations, etc. depends on use case. On Fri, Dec 11, 2020, at 6:02 AM, [email protected] wrote: > Dear sqlalchemy users, > > After look at the docs I arrived at the following structure: > > ``` > Base = declarative_base() > > > parent_child = Table( > "parent_child", > Base.metadata, > Column("parent_id", Integer, ForeignKey("parent.id")), > Column("child_id", Integer, ForeignKey("child.id")), > ) > > > class Parent(Base): > __tablename__ = "parent" > id = Column(Integer, primary_key=True) > children = relationship("Child", secondary=parent_child, backref="children") > > > class Child(Base): > __tablename__ = "child" > id = Column(Integer, primary_key=True) > ``` > > When I run the following query with a parent that has 300.000 children it > takes approximately 25 seconds to load the children. > > ``` > parent = session.query(Parent).filter_by(id=96).first() > children = parent.children > ``` > > Now the query it ends up doing when collecting the children is: > > ``` > app_1 | INFO:sqlalchemy.engine.base.Engine:{'id_1': 96, 'param_1': 1} > app_1 | INFO:sqlalchemy.engine.base.Engine:SELECT child.id AS child_id > app_1 | FROM child, child_parent > app_1 | WHERE %(param_1)s = child_parent.parent_id AND child.id = > child_parent.child_id > app_1 | INFO:sqlalchemy.engine.base.Engine:{'param_1': 96} > ``` > > It feels like this should be a join and not a cartesian product with a where > statement. What am I doing wrong? Or how can I make sure that it does a join > when fetching the children instead of this. > > Cheers > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/6c757c2d-f59b-41d6-950a-3b8e2f7e1731n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/6c757c2d-f59b-41d6-950a-3b8e2f7e1731n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/99905967-e280-454a-8396-91dd0e596817%40www.fastmail.com.
