問題描述

我有一個腳本需要臨時提取數(shù)據(jù)以對其進行額外操作，但在腳本運行后不需要進一步存儲它.我目前在一系列臨時本地表 (CREATE TABLE #table) 中有相關(guān)數(shù)據(jù)，然后在使用完成后將其刪除.我正在考慮切換到以相同方式處理的物理表(CREATE TABLE 表)，如果它的腳本速度會有所提高(或者其他優(yōu)勢，也許?).

I have a script that needs to extract data temporarily to do extra operations on it, but then doesn't need to store it any further after the script has run. I currently have the data in question in a series of temporary local tables (CREATE TABLE #table), which are then dropped as their use is completed. I was considering switching to physical tables, treated in the same way (CREATE TABLE table), if there would be an improvement in the speed of the script for it (or other advantages, maybe?).

...那么，臨時表和物理表在性能上有區(qū)別嗎?從我讀到的內(nèi)容來看，臨時表只是物理表，只有運行腳本的會話才能查看(減少鎖定問題).

...So, is there a difference in performance, between temporary tables and physical tables? From what I'm reading, temporary tables are just physical tables that only the session running the script can look at (cutting down on locking issues).

我應(yīng)該指出我在談?wù)撐锢肀砼c臨時表.有很多關(guān)于臨時表與表變量的信息，例如http://sqlnerd.blogspot.com/2005/09/temp-tables-vs-table-variables.html.

I should point out that I'm talking about physical tables vs. temporary tables. There is a lot of info available about temporary tables vs. table variables, e.g. http://sqlnerd.blogspot.com/2005/09/temp-tables-vs-table-variables.html.

推薦答案

臨時表是 SQL Server 中的一個大問題.

Temporary tables are a big NO in SQL Server.

• 它們會導(dǎo)致成本高昂的查詢計劃重新編譯.
• 創(chuàng)建和刪除表也是您添加到流程中的成本高昂的操作.
• 如果有大量數(shù)據(jù)進入臨時數(shù)據(jù)，您的操作將因缺乏索引而變慢.您可以在臨時表上創(chuàng)建索引.但我永遠不會為任何有大量記錄的東西推薦一個臨時表.

您的另一種方法:創(chuàng)建然后刪除常規(guī)表只會產(chǎn)生相同的開銷.

Your other approach: To create and then drop regular tables just creates the same overhead.

另一種方法:使用現(xiàn)有表，用附加列擴充行以區(qū)分可以使用與每個用戶/會話相關(guān)的行.消除了創(chuàng)建/刪除表的負(fù)擔(dān)，但是，您將需要對生成值以區(qū)分行的代碼保持偏執(zhí)，并且您必須開發(fā)一種方法來維護會話過早結(jié)束的情況下的表并且還有剩余部分(處理結(jié)束時未刪除的行).

Another approach: Using existing tables, augmenting the rows with an additional column to differentiate which rows pertain to each user/session could be used. Removes the burden to create/drop the tables but, then, you will need to be paranoid with the code that generate the value to differentiate the rows AND you will have to develop a way to maintain the table for those cases where a session ended prematurely and there are leftovers (rows that were not removed at the end of the processing).

我建議您重新考慮您的處理策略.一些替代方法就像使用相關(guān)查詢、派生表或表變量一樣簡單.看看:http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

I recommend you to rethink your processing strategy. Some alternatives are as easy as using correlated queries, derived tables or table variables. Take a look at: http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

創(chuàng)建和刪除常規(guī)表的方法以及重用帶有附加字段的常規(guī)表的方法:兩者都會生成查詢計劃重新編譯，因為更改的數(shù)據(jù)量將觸發(fā)重新評估表統(tǒng)計信息.同樣，您最好的方法是尋找其他方法來處理您的數(shù)據(jù).

The approach of creating and dropping regular tables and the approach of reusing a regular table augumented with an additional field: Both will generate query plan recompilations because the amount of data changed will trigger the reevaluation of table statistics. Again, your best approach is to find alternate ways to proccess your data.

這篇關(guān)于SQL中臨時表與物理表的比較速度是多少?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！