Should you use temporary tables to pass data between stored procedures?

Answer 1

The second should perform better. It will the smaller proportion of the data that you want to return.

The second gives the query optimiser the chance to do the query in any order it wants, the first forces a select from the first table first (which in your case is likely to be the one wanted).You can also use parallel queries on the second one ie more than one thread working on the query as the optimiser can do this.

To check on this run a Showplan (Sybase or SQL Server) or EXPLAIN (Iracle) etc to see the actual query generated.

Answer 2

As long as the temp table contents represent the final set of unique keys to be output, and no further trimming of the resultset is done afterwards, then it is a very efficient way of implementing your requirements.

Problems will only arise if the temp table contains an intermediate set of keys which is cut down further in subsequent queries. In that scenario, reading all the data in one query will be more efficient.

EDIT: As Mark says, there may be a performance difference due to the query optimiser being able to use multiple threads in the single query approach, but not in the temp table approach. You have to weigh this potential gain against the vast improvement in maintainability of the temp table approach, and decide which is more important for you. As usual with database questions, it's better to measure performance rather than guess at it.

Answer 3

If using Microsoft Sql Server, I prefer a third method:

Create Function GetSearchKeys([Search parameters here])
Returns @Keys Table (pk Int Primary Key Not Null)
As
Begin
      Insert @Keys(pk)
      Select C.CustomerID 
      From /**** do actual search here, based 
               on Search parameters ****/
      Return
End

  -- ----------------------------------------------------

And then, in each stored proc,

  SELECT col1, col2, col3, col4, .... etc, lots of columns ... 
  FROM table1 
     LEFT JOIN table 2 
        ON ... etc, lots of joins
     Join schema.GetSearchKeys([Search parameters here]) K
        on K.pk = [whatever table.column has the primary key in  it]