Sorry,
I thought I deleted the post just after sending it but it seems like it
still went through. I figured out it was indeed the loading of ORM objects
and not the query itself. For completeness sake in case anyone reads this
at a later date. We were doing something like len(parent.children) to count
the number of rows. We changed it to a column_property like so:
```
parent_child_count = column_property(
select([count(ParentChild.drawing_id)]).where(ParentChild.drawing_id ==
id),
deferred=True,
)
```
Thank you for your response regardless!
On Fri, 11 Dec 2020 at 14:50, Mike Bayer <mike...@zzzcomputing.com> wrote:
> 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, marnix....@gmail.com 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 sqlalchemy+unsubscr...@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/99905967-e280-454a-8396-91dd0e596817%40www.fastmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/99905967-e280-454a-8396-91dd0e596817%40www.fastmail.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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CA%2By%3D2bvKv%3DdLXhn5qTb-EriKfXnkSprSJEk7LHzuPtoKZbMkig%40mail.gmail.com.
