Nonclustered Index on Temp Table

Question

Post reply

Nonclustered Index on Temp Table

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 1

Goldie Lesser

SSCertifiable

Points: 6155

November 3, 2010 at 7:29 am

#1245981

Thanks to all of you for your help! 🙂

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

I am going to step outside the box here and ask WHY you want to put a nonclustered index on a temp table?? In almost 15 years of doing sql server work (and most of that as a performance tuning specialist) I can probably count on my two hands cases where a query actually BENEFITED from such. But I can count probably a few hundred times where I have seen clients create indexes on temp tables that were a complete waste and only slowed query performance.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

GSquared SSC Guru Points: 260824 More actions · Answer 3

TheSQLGuru (11/4/2010)

I am going to step outside the box here and ask WHY you want to put a nonclustered index on a temp table?? In almost 15 years of doing sql server work (and most of that as a performance tuning specialist) I can probably count on my two hands cases where a query actually BENEFITED from such. But I can count probably a few hundred times where I have seen clients create indexes on temp tables that were a complete waste and only slowed query performance.

I've had a few instances of indexes helping out on temp tables. Constraints are another matter. I've had those be useful many, many times.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sjimmo SSChampion Points: 11139 More actions · Answer 4

The SQLGuru

I can probably count on my two hands cases where a query actually BENEFITED from such. But I can count probably a few hundred times where I have seen clients create indexes on temp tables that were a complete waste and only slowed query performance

This is why it should be tested to see if there is a benefit. It also matters where the build is done, whether it is done before or after the data is loaded.

I have seen cases where there are multiple indexes on multiple temp tables but these have many rows and are being joined upon with permanent tables. This would reduce table scans thus increasing performance.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 5

TheSQLGuru (11/4/2010)

I am going to step outside the box here and ask WHY you want to put a nonclustered index on a temp table?? In almost 15 years of doing sql server work (and most of that as a performance tuning specialist) I can probably count on my two hands cases where a query actually BENEFITED from such. But I can count probably a few hundred times where I have seen clients create indexes on temp tables that were a complete waste and only slowed query performance.

I've found that they do help performance sometimes. Usually in cases with 2 or more large temp tables that need to be joined.

As GSquared pointed out, constraints on temp tables very often speed up performance.

Unfortunately, in this case the constraint was not helping so I was trying an index.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

>>I have seen cases where there are multiple indexes on multiple temp tables but these have many rows and are being joined upon with permanent tables. This would reduce table scans thus increasing performance.

That is exactly the issue that most of my clients THINK is true, yet almost always is not. You have to scan the big temp table (tempdb read) then build the index (tempdb write and possibly tempdb tlog write) and those can be costly evolutions in both time and cpu. And even after you did that, unless you have qualifications in your query that hits said temp table you will STILL do a table scan most of the time (in my experience). So you built an index for absolutely no benefit.

And if you are worried about doing a scan against a big permenent table where you might not otherwise then you can simply create a statistic against the join column or where clause columns on the temp table, which is much more efficient than index creation.

Again I can't count the number of times I have improved client code that built useless index(es) against temp tables.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

GSquared SSC Guru Points: 260824 More actions · Answer 7

Build cost vs use cost is an important part of determining whether to index a temp table. That's definitely true.

Most of the time, if a temp table should have a non-clustered index, it probably shouldn't be a temp table.

Not always true, but often.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sjimmo SSChampion Points: 11139 More actions · Answer 8

SQLGuru

That is exactly the issue that most of my clients THINK is true, yet almost always is not. You have to scan the big temp table (tempdb read) then build the index (tempdb write and possibly tempdb tlog write) and those can be costly evolutions in both time and cpu. And even after you did that, unless you have qualifications in your query that hits said temp table you will STILL do a table scan most of the time (in my experience).

Which is why I said that you need to test to see if there is a benefit. I agree that tehre are times where indexing a temp table is not beneficial. But your statement makes it sound like it is always a waste of time and resources.

Before I tell a developer to build an index on a temp table, I make sure that I have tested both ways and taken into consideration the resources required to perform the task against the final results. It is the needs of the business that ultimately dictates what is required to make this work. You cannot make an arbitrary decesion without testing.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

sjimmo (11/4/2010)

SQLGuru

That is exactly the issue that most of my clients THINK is true, yet almost always is not. You have to scan the big temp table (tempdb read) then build the index (tempdb write and possibly tempdb tlog write) and those can be costly evolutions in both time and cpu. And even after you did that, unless you have qualifications in your query that hits said temp table you will STILL do a table scan most of the time (in my experience).

Which is why I said that you need to test to see if there is a benefit. I agree that tehre are times where indexing a temp table is not beneficial. But your statement makes it sound like it is always a waste of time and resources.

Before I tell a developer to build an index on a temp table, I make sure that I have tested both ways and taken into consideration the resources required to perform the task against the final results. It is the needs of the business that ultimately dictates what is required to make this work. You cannot make an arbitrary decesion without testing.

I am pretty sure that I have numerous statements that contradict your "always" statement, but no matter.

Given how many devs there are out there that have very poor/no skills at query tuning, I can, based on 'my' (pretty extensive) experience, make the statement that probability is hugely in favor of not creating indexes on temp tables being more performant than creating them. If the client is lucky enough to have a tuner on board, he/she can do the testing to fix the handful of cases where it is better. Again in my experience most companies do not have such a person.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

sjimmo SSChampion Points: 11139 More actions · Answer 10

SQLGuru

Again in my experience most companies do not have such a person

Which worked out great for me - as these were the reasons that my company got called in to fix the problems over and over.

It is still amazing that you do not need to test your decision before applying it, based upon your vast experience.

In my over 25 years of experience, I would never make such a global remark without saying that it depends upon the results of testing to see whether in this particular case that an index was needed to improve overall performance or not.

But then, I am still learning. Thank you for the lesson.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Graeme Martin SSC Veteran Points: 294 More actions · Answer 11

Graeme Martin

SSC Veteran

Points: 294

September 29, 2015 at 2:10 pm

#1830277

You don't actually need to name the PK:

CREATE TABLE #Test (ID INT IDENTITY);



ALTER TABLE #Test ADD PRIMARY KEY CLUSTERED (ID);

- or -

CREATE TABLE #Test



(

	ID INT IDENTITY

	, SomeCharVal CHAR(10)

	, [Description] VARCHAR(255)

	, PRIMARY KEY CLUSTERED (ID)

);