SQL 临时表在不同的 SQL 读取器之间共享

Question

我正在尝试对创建时间非常长的结果集执行许多不同的查询。 为了获得性能提升，我希望使用临时表并在此临时表上执行许多查询。

看起来很标准。 然而我正在努力在动态 SQL 中共享这个临时表。 据我了解，每个 SqlCommand 对象都在自己的线程中执行，因此临时表位于不同的范围内 - 从而使其无法从查询线程访问。

我尝试使用全局临时表，效果很好，但不理想？

如何在动态 SQL 查询之间共享本地临时表？

我的意图：

using (var conn = new SqlClient.SqlConnection("..."))
{
    // Creation involes many table joins in reality
    String creationScript = "SELECT * FROM FooTable INTO #MyTemp";
    SqlCommand createTempTbl = new SqlCommand(creationScript, conn);
    createTempTbl.ExecuteNonQuery();

    String query1 = "SELECT * FROM #MyTemp where id=@id";
    SqlCommand query1Comm = new SqlCommand(query1, conn);
    query1Comm.Parameters.Add("@id", ...);

    String query2 = "SELECT * FROM #MyTemp where name=@name";
    SqlCommand query2Comm = new SqlCommand(query2, conn);
    query2Comm.Parameters.Add("@name", ...);

    // And so on the queries go

} // Now want #MyTemp to be destroyed

Answer 1

我知道这篇文章发布已经有一段时间了，但我相信答案很简单。

我猜测您正在使用 MS Enterprise Library 来访问数据库，这解释了为什么命令之间不存在临时表。 当命令完成时，企业库明确关闭与数据库的连接（将其放回到池中）。 也就是说，除非您将命令放入事务中。
如果您直接使用 ADO.NET（通过打开连接、构建并执行命令，然后关闭连接），则不会出现此问题（由您决定连接何时关闭 - 这风险更大）。
以下是使用 MS 企业库和事务（抱歉，VB.NET）编写的一些代码：

' Get a reference to the database
Dim sqlNET As New Sql.SqlDatabase("*Your Connection String Here...*")

' Specify the transaction options
Dim oTranOpt As TransactionOptions = New TransactionOptions
' What type of isolation the transaction should have (V. Important):
oTranOpt.IsolationLevel = IsolationLevel.ReadUncommitted ' This one doesn't place locks on DB but allows dirty reads
' How long the transaction has to complete before implicitly failing (h,m,s):
oTranOpt.Timeout = New TimeSpan(0, 0, 30)

' Start the scope of the transation
Using oTranScope As TransactionScope = New TransactionScope(TransactionScopeOption.Required, oTranOpt)

    ' Create the connection to the DB. Not abs. necessary. EL will create one but best to do so.
    Using Conn As Common.DbConnection = sqlNET.CreateConnection

        ' Create a Temp Table
        sqlNET.ExecuteNonQuery(CommandType.Text, "SELECT * INTO #MyTemp FROM FooTable")

        ' Get results from table, e.g.
        Dim intCount As Integer = sqlNET.ExecuteScalar(CommandType.Text, "Select Count(*) from #MyTemp")

        MsgBox(intCount)

        ' Flag transaction as successful (causes a commit when scope is disposed)
        oTranScope.Complete()

    End Using ' Disposes the connection

End Using ' If this point is reached without hitting the oTranScope.Complete - the transaction is rolled back and locks, if any, are released.

如果要删除事务范围，则代码将在 Select count(*) 上失败，因为表不再存在。 指定范围使命令调用之间的连接保持打开状态。

我希望这可以帮助别人。

尼尔.

Answer 2

您可以尝试使用全局临时表（即，在查询中使用 ##MyTemp 而不是 #MyTemp），有此警告：

全局临时表是
会话时自动丢弃
创建表结束和所有
其他任务已停止引用
他们。 任务之间的关联
并且仅维护一个表
单个 Transact-SQLTransact-SQL 的生命周期
陈述。 这意味着全球
临时表被删除在
完成最后一个 Transact-SQLTransact-SQL
积极的声明
参考该表时
创建会话结束。

---

编辑：糟糕，错过了您已经尝试过全局临时表的事实。

如何将所有逻辑移至单个存储过程中，该存储过程创建/填充临时表，然后运行查询并向客户端代码返回多个结果集？

Answer 3

您的问题缺少的是创建的表的生命周期。 如果您将其保留一段时间，那么它不完全是临时表，而是您填充和使用的工作表。 我根本不会使用临时表，只是一个由 SELECT INTO 创建并由其他人使用的常规表，直到它被删除（如果有的话）。

Answer 4

我成功使用的另一种方法是在 TempDb 中创建一个工作表，并像使用全局临时表一样使用它（例如“TempDb.dbo.MyTable”）。 请记住，当 SQL Server 重新启动时，用户表将被删除。