I'm trying to optimise a slow performing CTE, the raw SQL is fast but I'm
having trouble getting the generated SQL from SA to be the same. My issue
is that the generated SQL contains 3 outer, self-referencing joins which
slow it down dramatically.
Here's the SQL I'm aiming for:
WITH RECURSIVE related_ledger(guid, created_at, reference_ledger_guid) AS (
SELECT ledger.guid AS guid, ledger.created_at AS created_at,
ledger.reference_ledger_guid AS reference_ledger_guid
FROM ledger
WHERE ledger.guid = %(guid_1)s
UNION ALL
SELECT ledger.guid AS ledger_guid, ledger.created_at AS
ledger_created_at, ledger.reference_ledger_guid AS
ledger_reference_ledger_guid
FROM ledger, related_ledger AS rla
WHERE ledger.reference_ledger_guid = rla.guid
)
SELECT related_ledger.guid AS related_ledger_guid,
related_ledger.created_at AS related_ledger_created_at,
related_ledger.reference_ledger_guid AS related_ledger_reference_ledger_guid
FROM related_ledger;
And here's what my CTE within python looks like:
related_ledger = Ledger.query.filter(
Ledger.guid == self.guid
).cte('related_ledger', recursive=True)
related_ledger_alias = aliased(related_ledger,
name='rl')
rec = Ledger.query.filter(
Ledger.reference_ledger_guid == related_ledger_alias.c.guid
)
union = related_ledger.union_all(rec)
Now I've got two ways to return the data as SA objects (as opposed to
straight tuples, with which this query is zippy):
Ledger.query.select_from(union)
This is slow as it generates something like this:
with related_ledger as (
...
)
select ...
FROM related_ledger
LEFT OUTER JOIN ledger AS ledger_1 ON related_ledger.guid =
ledger_1.reference_ledger_guid
LEFT OUTER JOIN ledger AS ledger_2 ON ledger_1.guid =
ledger_2.reference_ledger_guid
LEFT OUTER JOIN ledger AS ledger_3 ON ledger_2.guid =
ledger_3.reference_ledger_guid
I also tried
Ledger.query.from_statement(union.select())
I would expect this to be fast since it's mapping the original columns in
the query to what the object expects. However, I'm having trouble with this
syntax and getting this error:
** NoSuchColumnError: "Could not locate column in row for column
'ledger.guid'"
This appears to be because the table is aliased as reference_ledger.
So my questions are:
1. Am I writing the CTE in the wrong way to be the select_from method?
2. How can I use the from_statement method to work with an aliased table?
Thanks!
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/GScftyDX5pMJ.
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.
