Home > Uncategorized > TEMPDB Temp tables vs Permanent tables

TEMPDB Temp tables vs Permanent tables

I was testing some code on a client site this past week where we were populating temp tables and when I unit tested it, it ran in 15 minutes. Then we decided to make them permanent tables within TEMPDB and the process ran for over an hour. I posted some questions to the SQL Server Community and was able to get a resolution thanks to Paul White (http://sqlblog.com/blogs/paul_white) and Jorge Segarra (www.sqlchicken.com)and below was the original script

--15 minutes
CREATE TABLE #TWPA
(
	AccountEntity  VARCHAR(3) NOT NULL,
	AccountProductCD  VARCHAR(3) NOT NULL,
	AccountNum  VARCHAR(21) NOT NULL,
	PartyID VARCHAR(11) NOT NULL,
	CONSTRAINT PK_#TWPA PRIMARY KEY CLUSTERED
	(
		AccountEntity,
		AccountProductCD,
		AccountNum,
		PartyID
	)
)
GO
INSERT INTO #TWPA

	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table1		--9,109,553 rows
	UNION     
	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table2		--137,696,784 rows

GO


--Over an hour
CREATE TABLE tempdb.dbo.TWPA
(
	AccountEntity  VARCHAR(3) NOT NULL,
	AccountProductCD  VARCHAR(3) NOT NULL,
	AccountNum  VARCHAR(21) NOT NULL,
	PartyID VARCHAR(11) NOT NULL,
	CONSTRAINT PK_#TWPA PRIMARY KEY CLUSTERED
	(
		AccountEntity,
		AccountProductCD,
		AccountNum,
		PartyID
	)
)
GO
INSERT INTO tempdb.dbo.TWPA

	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table1		--9,109,553 rows
	UNION     
	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table2		--137,696,784 rows


GO
</code>

The resolution is the permanent table in TEMPDB was not using minimal logging like the temp table did.  To resolve this I had to put WITH (TABLOCKX) into the insert statement and it ran in 15 minutes just like the temp table version

<code>
INSERT INTO tempdb.dbo.TWPA WITH (TABLOCKX)

	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table1		--9,109,553 rows
	UNION     
	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table2		--137,696,784 rows


GO
Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: