CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
mel
mel

Reputation: 115

Run expensive query only once and return multiple results from it

I have an expensive query that gets all rows from a query with many joins. With this query I need to return two results:

  1. All the data from the query itself, limited at n rows
  2. Arrays of distinct values of specific columns from the query.

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:

  1. Create a temp table with the base query, query it twice to return the two operations, and then drop it
  2. Run the two operations separately and thus run the base query twice.

This seems like it should be a common problem in SQL. Am I misunderstanding how queries are optimized?

Upvotes: 1

Views: 104

Answers (2)

Ismail Diari
Ismail Diari

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:

  • do you have the correct indexes in places?
  • are your stats updated?
  • do you write correctly your where clauses so that they are Sagrable?
  • have you check your execution plan for other type of optimizations( to check your joins, missed indexes, stats)

Upvotes: 0

Gordon Linoff
Gordon Linoff

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

Related Questions