Index on Temp Table

[davism]

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.

 

[johnherman]

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)

 

[gmmastros]

+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

 

[davism]

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?

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[johnherman]

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)

 

[davism]

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.

 

[johnherman]

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)