Menu
My background process is bulk updating/inserting data. I need to store some data in temp table for later use. I have 5 temp tables. If I use temporary table(CREATE TABLE #T) then it will take 2-3 seconds but if I use table variable(DECLARE @T TABLE) then it takes more than 90 seconds. Here is a sample temp table,
CREATE TABLE #TempAttributes
(
AID int
,PID int
,ATypeValue nvarchar(MAX)
,ATypeKey nvarchar(MAX)
,PLanguageID int
);
Why table variable is very slow?
759
However, if there is a memory pressure the pages belonging to a table variable may be pushed to tempdb. ⇒ Table variables cannot be involved in transactions, logging or locking. This makes @table faster then #temp. So table variable is faster then temporary table.
Summary of Performance Testing for SQL Server Temp Tables vs. Table Variables. As we can see from the results above a temporary table generally provides better performance than a table variable. The only time this is not the case is when doing an INSERT and a few types of DELETE conditions.
It says that temp tables are always on disk, and table variables are in memory, that is to say, the performance of table variable is better than temp table because table variable uses less IO operations than temp table.
The reason, temp tables are faster in loading data as they are created in the tempdb and the logging works very differently for temp tables. All the data modifications are not logged in the log file the way they are logged in the regular table, hence the operation with the Temp tables are faster.
I guess it is because the Table Variable insertion does not support the parallel plan.
Parallel Query Processing is a proccess of optimization in parallel queries that is implemented in MsSql and the temporary table benefits from it whereas the Table Variable does not.
This is one of the main reasons why we do not use a table variable for large set of data (when the scope is of no concern).
Look here for more information.
121
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
