是否应该使用临时表在存储过程之间传递数据？

Question

我有许多搜索函数（存储过程），它们需要返回具有完全相同列的结果。

这是采用的方法：

每个存储过程都有一般结构：

CREATE TABLE #searchTmp (CustomerID uniqueIdentifier)

INSERT INTO #searchTmp
SELECT C.CustomerID FROM /**** do actual search here, based
                              on stored proc arguments ****/

EXEC spSearchResults

DROP TABLE #searchTmp

在上面，spSearchResults 在选择中使用#searchTmp 表。 spSearchResults 总是返回一个具有相同列的表，并且有相当多的连接。

但是，与使用临时表相比，以下方法更可接受：

SELECT col1, col2, col3, col4, ....等等，很多列... FROM table1 LEFT JOIN table 2 ON ...等等，很多连接... 在哪里...在这里进行实际搜索...

如果需要进行 10 次不同的搜索（例如，根据邮政编码搜索客户、根据姓氏搜索一次等），则第二种方法意味着存在大量重复的列和指定的联接。如果使用搜索函数的代码发生更改，从而需要返回新列，则需要更新 10 个存储过程。

我完全赞成第一种方法，但我只是想知道第二种方法有什么好处。表现？

或者还有第三种方法？

Answer 1

只要临时表内容代表要输出的最终唯一键集，并且之后不再对结果集进行进一步的修剪，那么它就是实现您的要求的一种非常有效的方法。

仅当临时表包含一组中间键且在后续查询中进一步减少时才会出现问题。在这种情况下，在一个查询中读取所有数据会更加高效。

编辑：正如 Mark 所说，由于查询优化器能够在单个查询方法中使用多个线程，但不能在临时表方法中使用多个线程，因此可能会存在性能差异。您必须权衡这一潜在收益与临时表方法可维护性的巨大改进，并决定哪一个对您更重要。与通常的数据库问题一样，最好测量性能而不是猜测性能。

Answer 2

第二个应该表现更好。它将是您想要返回的数据的较小部分。

第二个使查询优化器有机会按照它想要的任何顺序执行查询，第一个强制首先从第一个表中进行选择（在您的情况下可能是想要的）。您还可以在第二个，即多个线程处理查询，因为优化器可以执行此操作。

要检查此情况，请运行 Showplan（Sybase 或 SQL Server）或 EXPLAIN (Iracle) 等以查看生成的实际查询。

Answer 3

如果使用 Microsoft Sql Server，我更喜欢第三种方法：

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

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

然后，在每个存储过程中，

  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]