Reputation: 115
I have an expensive query that gets all rows from a query with many joins. With this query I need to return two results:
These two operations use the exact same base query. I don't want to run the base query twice since it is expensive, but I don't want to store it as a table, since I don't need it after these two operations have been returned and also the query happens often. I have a hack where I use CTE to run the query and then union my two operations into the same result. This poses some problems of needing to add new mostly null columns because the second operation column types (postgres arrays) are not the same as any of the base query column types. It also seems really hacky. It seems like the only two other solutions are to:
This seems like it should be a common problem in SQL. Am I misunderstanding how queries are optimized?
Upvotes: 1
Views: 104
Reputation: 519
Am I misunderstanding how queries are optimized?
Query Optimization is by far out of what you are exposing as candidat solutions. Query optimization start by analyzing why your query is slow, and for that you need to answer some question:
Upvotes: 0
Reputation: 1271231
You know the solution: create a temporary table and then delete it.
I wouldn't say this is a common problem in SQL, but SQL definitely supports it. And you understand what to do. If you need to persist results, then use a temporary table.
Upvotes: 2