On Sep 5, 2010, at 7:41 PM, Alec Munro wrote:
> Hi list,
> I've got a tree-type of data, representing somewhat of a method call
> history. When this is loaded, in an instance where it represents about
> 60 entries total, up to 2 deep, representing 14k of data, it takes
> about 9 seconds (up from ~0). Now, my laptop HDD where I am testing
> now is pretty terrible, but there's probably also some kind of
> optimization I could do.
> When I retrieve the data, I convert it to JSON, with a call like this
> (where json_test_case_method is a recursive function I defined):
>
> "methods":[json_test_case_method(method) for method in
> test_case.methods]}
>
> The hierarchical relationship is defined thusly:
>
> TestCaseMethod.children = relation(TestCaseMethod, cascade="all",
> backref=backref("parent",
>
> remote_side=[TestCaseMethod.id]),
> order_by=TestCaseMethod.id)
>
> I've done zero SQLAlchemy optimization in my career thus far, and very
> little ORM optimization (a bit with Hibernate several years ago), so
> any advice is appreciated. :)
You want to use "subqueryload()" or alternatively "joinedload()". The former
is probably best as it will emit one query per level of hierarchy, for the
whole load, fetching only as many rows as there are nodes. joinedload() can
load the whole thing in one query but it will see many duplicate parent rows go
by in result sets, which most DBAPIs pull over the wire unconditionally even if
skipped.
That said, even if you were emitting a distinct query for every node (which
would be the case if your tree were one root node 60 levels deep), 9 seconds
sounds very slow. On my macbookpro, I just ran a test that emits 60 distinct
ORM queries for unloaded objects, each with a string field 1000 characters in
length, and it takes .03 seconds. So you might want to poke around at your
network or some other logic that's taking way too much time in there (profiling
would be a good start - here's a big post I wrote about it -
http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677
).
>
> Thanks,
> Alec
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
