INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Index on Temp Table

Index on Temp Table

(OP)
Hi,


In SQL Server 2008 R2 and up, is it a performance issue if in a stored procedure a temp table is created, then loaded, then an index created on it?

Or should it create that a temp table is created, an index created on that temp table and then loaded?

Also, if it not true that IF after subsequently done in this matter, if the temp table is used in any way in that stored procedure the optimizer would not even know about the existence of the index on that temp table.

It should be that a parent stored procedure, creates the temp table, creates the index, loads it and then calls a child stored procedure and in that child stored procedure it uses that temp table?

Any confirmation on this would be greatly appreciated.

RE: Index on Temp Table

In most databases, it is more efficient to create a table (temporary or not), then load the table, then create the index. Might be true for all databases, but I only have experience with Oracle, DB2, SQL Server, Vertica, Netezza, and several outdated databases.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive agile big data clouds)


RE: Index on Temp Table

+1 for John's statement. That's been my experience too.

Of course... it usually doesn't hurt to test it both ways.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Index on Temp Table

(OP)
I suppose I'm missing the point here especially if the table is not a temp table. I mean, I have previously dealt generated from persistent tables in the order of millions of rows to generating a small subset to the of 10's of thousands. Our DBA always told us to create the temp table and then index, then load. The indexing worked best with data already in existence.

Also, is it not true that IF after subsequently done in this matter, if the temp table is used in any way in that stored procedure the optimizer would not even know about the existence of the index on that temp table.

It should be that a parent stored procedure, creates the temp table, creates the index, loads it and then calls a child stored procedure and in that child stored procedure it uses that temp table? And I actually has a PDF document, if I remember right, from MS, citing that exact situation but it seems people just don't do it.

But is any of that the case or have the perceivably reputable DBA's that told me that not so knowledgeable?

RE: Index on Temp Table

Davism, do not go from what you may have heard or read anywhere - always always try it out with real volumes of data and on machines with specs near enough those of production.

a proc that creates a table then creates an index on that table will have its index considered and used in any reference to that table both on the remaining of the proc and on any proc that is called by the one that defines the table and index. whether it uses it or not is determined by many factors - but same is true for any other permanent index on any permanent table.

my normal advise for these is to do as follows and always with full volumes of data
for each step take note of its duration

create table
load data (with tablockx if using compression)
create indexes as required

test it, analyze performance, see explain plan, io statistics etc. all normal performance stuff

swap order and test it again

add a clustered index - test it again

if on enterprise edition add compression to both table and indexes - test it again

it may be that in some cases the following order is best
create table (with compression if best/applicable)
create clustered index (with compression if best/applicable)
load data
create remaining indexes

on others load may be faster if done before the indexes.

but as the others said, try it as each case needs its own approach.

when you are testing you need to take in consideration not only the elapsed time but also the load on the server of the process in question.
you may need to choose between a process taking 20 minutes and having a 20% load on the server, or taking 5 minutes and hammering the system (which well get the DBA's on you with a big hammer)

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Index on Temp Table

Of course, Frederico has the best answer. For all the theory and best practices that you can read, there is NO SUBSTITUTE for doing a proof of concept in your own computing environment.

Also of note: If there are very few records in the temporary table, it may actually be detrimental to build an index. For small tables, it is more efficient to do a full table scan rather than an indexed read. For instance, a table with valid States and Territories with 100 or so rows probably should not have an index.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive agile big data clouds)


RE: Index on Temp Table

(OP)
Yep, understand that. It's basically saying the knowledge of the DBA's as general practice is not wise. Although, they had run things very well in all the different environments for varying sizes.

It appears you all are saying there is no general practice on SQL Server. Basically a "try before you buy" type scenario. You just cited a index situation on small tables or lack thereof. Again, "try before you buy" type thing. By the way, our DBA's told us 1000 rows.

It may be more wise to ask them.

Thank you all for the time and info; its of value to get more information and opinions.

RE: Index on Temp Table

Being able to know that your solution will work because you tested it on your system is a very valuable stance.

The number of rows where an index may not provide value also depends on the rowlength, as well as the number of rows. Essentially, if the whole table can be read as one "page read", then don't bother with the index.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive agile big data clouds)


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close