In which cases can INNER JOIN (SELECT ... not be rewritten using temp table

Question

I am using GridSQL where I get some performance problems whenever the SQL pattern INNER JOIN (SELECT arises. I am therefore considering rewriting all these queries into two queries, one creating a temporary table using the exact select statement and the other query joining with the temporary table, so the pattern would be INNER JOIN temp_table (...) instead.

In which cases would this not work? As you might suspect, I am hoping for no answers to this question. ;)

Answer 1

The only time a sub-select can't easily be broken off into a temp-table is when it references fields in the outer table.

For "... INNER JOIN (SELECT ..." I doubt you would have that issue (or if it's even possible, I don't recall ever trying it), since you should only be referring to fields from the first table in the join criteria.

Answer 2

I don't think a temp table will speed up your queries! Instead you should optimize your queries. Things you can do:

• Filter the records before inner joining them with other record sets
• Define proper indexes

-Pavel

Answer 3

In which cases can INNER JOIN (SELECT … not be rewritten using temp table

if the query you are replacing with a temp table can result in a huge result set, I would avoid using a temp table. The overhead generating it would negate any possible gain.