Temp Table vs. Physical Table Performance

Last Post 29 Jul 2014 07:49 AM by rerichards. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
rerichards
New Member
New Member

--
28 Jul 2014 08:30 AM
I have noticed that inserts into a non-indexed physical table are taking much longer than inserts into a non-indexed temporary table.

As a way of testing I have this script that generates int values from 1 to 100,000,000 and inserts those values into a physical table and temporary table, each being non-indexed and having a single int data type column.

The following is the test script:

--Physical Table creation and Insert
IF OBJECT_ID('dbo.InsertTest') IS NOT NULL DROP TABLE dbo.InsertTest;
CREATE TABLE dbo.InsertTest(N bigint);
TRUNCATE TABLE dbo.InsertTest;

WITH
E1(N) As(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), --10 SELECTs, 10 rows
E2(N) As(SELECT 1 FROM E1 a, E1 b), --100 rows
E4(N) As(SELECT 1 FROM E2 a, E2 b), --10,000 rows
E16(N) As(SELECT 1 FROM E4 a, E4 b), --100,000,000 rows
CTETally(N) As (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)

INSERT dbo.InsertTest
SELECT N
FROM CTETally;

GO

--Temporary Table creation and Insert
IF OBJECT_ID('TempDB..#TInsertTest') IS NOT NULL DROP TABLE #TInsertTest;

CREATE TABLE #TInsertTest(N bigint);

WITH
E1(N) As(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), --10 SELECTs, 10 rows
E2(N) As(SELECT 1 FROM E1 a, E1 b), --100 rows
E4(N) As(SELECT 1 FROM E2 a, E2 b), --10,000 rows
E16(N) As(SELECT 1 FROM E4 a, E4 b), --100,000,000 rows
CTETally(N) As (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)

INSERT #TInsertTest
SELECT N
FROM CTETally;

The following are the test results with the Duration in milliseconds:
Physical Table Reads: 117,122,083
Temporary Table Reads: 107078
% Reads Difference: 99.91

Physical Table Duration: 644,710
Temporary Table Duration: 55,087
% Duration Difference: 91.46

Physical Table CPU: 500,716
Temporary Table CPU: 82,180
% CPU Difference: 83.59

Both the Physical Table and Temporary Table are located on a NetApp SAN, with each using a modified RAID 4 configuration.
The physical table resides in a database with a Full Recovery model.
The Server is Windows Server 2008 Enterprise SP2
128 GB RAM
The SQL version is SQL 2008 Enterprise SP3

I understand that Temporary Tables are minimally logged.

However, does the difference in the above metrics seem normal?
gunneyk
New Member
New Member

--
28 Jul 2014 10:19 AM
There may be a few factors involved here but one is that yes the temp table insert is most likely minimally logged since tempdb is in Simple recovery mode. And yes minimal logging can be a HUGE difference in the amount of data written to the tran log. You can test to see if this is mostly tran log writes by either taking a snapshot of the virtual file stats DMV before and after the insert and looking at the delta. Just make sure nothing else is using that. Another way is to look at the number and size of the log records written to the tran log. Run this in the context of each db before and after:
select COUNT(*) AS [# Recs],sum ([LOG RECORD LENGTH]) AS [Rec Length]
from fn_dblog(null, null)

Also other activity can certainly play a role in tests like this especially when it comes to log write performance. And the number of VLF's in the log file can also add to this as well. But the majority of it is most likely the minimal logging.
rerichards
New Member
New Member

--
28 Jul 2014 10:51 AM
So the summary of all this (with all things (such as hardware) being equal and this being the only active operation on the box) is that writing to fully logged non-indexed physical table is much more expensive in terms of Reads, Duration, and CPU, in comparison to writing to a non-indexed temporary table? Correct?
gunneyk
New Member
New Member

--
28 Jul 2014 11:20 AM
Well I would say that writing to a table with full logging is much more expensive than writing to a table with minimal logging. This is true of temp tables and real tables. It's mostly the difference between FULL recovery and SIMPLE or BULK-LOGGED recovery modes. The fact that it is non-indexed is a separate issue. You can still get minimal logging with a table and index under the right circumstances but not in FULL RECOVERY mode.
rerichards
New Member
New Member

--
28 Jul 2014 12:32 PM
I just realized that the physical table mentioned above and its associated metrics were located on a database with a recovery model of Simple.

Now I am really confused why the huge difference in Reads, Duration, and CPU.

Any idea what could be leading to the huge difference between inserting into the physical table and inserting into the temporary table?

There are no other processes on the box, just the script executing in the earlier thread.
rm
New Member
New Member

--
29 Jul 2014 05:59 AM
User db and tempdb have same number of data files? Are they on same volume with same channel?
rerichards
New Member
New Member

--
29 Jul 2014 07:49 AM
The UserDB has a single data file in both Dev and Production environments.
TempDB in Dev has a single data file
TempDB in Production has 8 data files.

In both Dev and Production environments the UserDB and TempDB reside on different volumes and different channels.

I see the same (very similar) results in both Dev and Production.

You are not authorized to post a reply.

Acceptable Use Policy