iT邦幫忙

3

[C#][MSSQL] 連線結束後 Temp Table 沒有自動釋放 !?

原文:[C#][MSSQL] 連線結束後 Temp Table 沒有自動釋放 !?

上次 [C#] SQL 資料庫 Connection Pool 連線池觀念釐清 有提到可以開啟 Connection Pool 來減少開啟連線的效能耗損,但最近發現有 Stored Procedure 執行後沒有手動 Drop Temp Table,而 .NET 又將連線丟回 Pool 裡面造成 Temp Table 沒有被正常釋放的問題,下面來測試看看是不是真的會有這種情況發生。

正常情況下 Temp Table 的生命週期為一個 Connection,當 Connection 結束時將會回收中間使用到的暫存資源,但是 .NET 內建的 Connection Pool 的功能讓 Connection 重複利用且沒有關閉,如果是 Web Applicetion 或是 Windows Service 沒有手動 Drop Temp Table 就會發生資源被占用的問題。

這邊為了測試我將 Pooling 設為 True 並將 MinPoolSize 設為 100 更容易看出差異

程式碼:

static void Main(string[] args)
{
    SqlConnectionStringBuilder sqlStrBuilder = new SqlConnectionStringBuilder();
    sqlStrBuilder.DataSource = "192.168.245.137";
    sqlStrBuilder.UserID = "sa";
    sqlStrBuilder.Password = "Zxcv#1234";
    sqlStrBuilder.InitialCatalog = "master";
    sqlStrBuilder.Pooling = true;
    sqlStrBuilder.MinPoolSize = 100;

    string connectionString = sqlStrBuilder.ToString();

    for (int i = 0; i < 100; i++)
    {
        string result = ExecuteSql(connectionString, i);
        Console.WriteLine(result);
    }

    Console.ReadKey();
}

static string ExecuteSql(string connectionString, int i)
{
    string tempTableName = $"#{Guid.NewGuid().ToString().Replace("-", string.Empty)}";

    var sb = new StringBuilder();
    sb.AppendLine($"SELECT '{i} - {tempTableName}' AS col INTO {tempTableName}");
    sb.AppendLine($"SELECT col FROM {tempTableName}");

    using (var sqlConnection = new SqlConnection(connectionString))
    {
        string sql = sb.ToString();
        return sqlConnection.ExecuteScalar<string>(sql);
    }
}

Temp Table 占用狀態:

結論:

只要不結束應用程式這些 Temp Table 就不會被釋放,如果資料量大的話會造成硬碟空間被大量占用甚至出現吃滿的狀況。

從這件事可以了解使用 Temp Table 完畢後記得要手動 Drop 掉,不然可能會有效能問題。


1 則留言

0
PPTaiwan
iT邦新手 5 級 ‧ 2019-05-31 16:07:10

幾個問題點想請問...

  1. 為何要透過 .NET Connection 下達整個 TempTable 的應用? 為何會用 TempTable 在什麼情況下或是需求會用到。

為何不先做

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP table #TempTable
GO

  1. 為何不用 Declare @TmpTable Table 的方式來去做這樣子的動作??
  1. 這個範例只是拿來測試 Temp Table 是否會釋放的 testing code
    實務上使用 Temp Table 通常是在 Stored Procedure 裡面使用
    舉例來說:

有 TradeOrder, TradeOrder_Detail 兩張 Table,有支排程需要定期用 TradeOrder_Detail.TransferStatus 的資料去更新 TradeOrder.Status,這時我會先將篩選後的 TradeOrder_Detail.TradeOrderId 儲存至 Temp Table,再利用這些 Primary key 去 Update TradeOrder.Status

當然你可能會疑惑為什麼不用 UPDATE FROM 或是 CURSOR 來去達到相同的目的呢?
就我所知 UPDATE FROM 與 CURSOR 會讓 Deadlock 的機率大幅增加
所以我會選擇先用 WITH NOLOCK 或是 WITH UPDLOCK 的方式將要更新的資料撈出再去用 Primary key 更新避開 Deadlock 並增加更新速度

如果我做完這些事情沒有 DROP Temp Table 那這些資料就不會被清除直到 Connection 結束,所以這件事情跟

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP table #TempTable
GO

這句沒有什麼特別的關聯

  1. Declare @TmpTable Table 是儲存在記憶體中效能當然較好,但如果資料量一大時記憶體會被吃光光,所以我視資料量大小選擇使用。
ezdoc iT邦新手 2 級‧ 2019-06-03 18:03:55 檢舉

Table variable 要看使用情境,有時效能會比 temp table 差。參考

我要留言

立即登入留言