best way to create an index in a temp table

Question

Post reply

best way to create an index in a temp table

DBA-640728

SSChampion

Points: 12898
More actions
October 10, 2008 at 6:58 am

#194432

hi guys i have a couple of temp tables in a very high CPU stored procedure, so i decided the best way to optimize it would be to add indexes to the temp tables. My question is which one of these three ways is it best: I tried using the execution plan but i don't see much difference.
CREATE TABLE #temp_employee_v1
(emp_id int not null
,lname varchar (30) not null
,fname varchar (30) not null
,city varchar (20) not null
,state char (2) not null )
CREATE UNIQUE CLUSTERED INDEX IX_1 on #temp_employee_v1 (lname, fname, emp_id)
or
CREATE TABLE #temp_employee_v2
(emp_id int not null
,lname varchar (30) not null
,fname varchar (30) not null
,city varchar (20) not null
,state char (2) not null
,PRIMARY KEY (lname, fname, emp_id)
or
Select emp_id, lname, fname,city, state
into #temp_employee_v3
from employeedetails
CREATE UNIQUE CLUSTERED INDEX IX_1 on #temp_employee_v3 (lname, fname, emp_id)

Viewing 6 posts - 1 through 5 (of 5 total)

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

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

Select emp_id, lname, fname,city, state

into #temp_employee_v3 from employeedetails

CREATE UNIQUE CLUSTERED INDEX IX_1 on #temp_employee_v3

(emp_id,lname, fname)

Make the emp_id as first column in index column and check.

DBA-640728 SSChampion Points: 12898 More actions · Answer 2

thank you, can you please tell me why would this way be faster?

DBA-640728 SSChampion Points: 12898 More actions · Answer 3

another two questions if you don' t mind, is it neccesary to do this before creating the clustered index for the temp table?

IF EXISTS (SELECT name FROM sysindexes WHERE name = IX_1)

DROP INDEX #temp_employee_v3.IX_1

CREATE UNIQUE CLUSTERED INDEX .....

also, in this case would it be better to have those three columns in the clustered index (emp_id,lname, fname) or just emp_id?

thanks in advance 🙂

Luke L SSC-Dedicated Points: 35547 More actions · Answer 4

I believe the Select INTO prior to the Index creation was suggested because you then don't have to use the resources involved in maintaining the index during your table population. This depends on on just how much data your adding if it would or would not be significant. Your Mileage May Vary.

Since this is a temp table, your shouldn't really need to run the If Exists drop index statement, because you just created the table with your select into statement, right? There should be no indexes that exist on it yet. Now if you're doing some error checking and running this over and over again you'll need to drop it before you create it again, but remember the table and indexes only exist local to your connection, so as soon as the connection is broken, the temp table should no longer exist.

as for which columns are listed in the index, sure include whatever you need to make sure that your index is used. The employeeID should be listed first though because I would imagine that's what actually make the record unique. Including the LastName and FirstName columns just helps retrieval because you don't need bookmark lookups.

To help us help you read this[/url]For better help with performance problems please read this[/url]

DBA-640728 SSChampion Points: 12898 More actions · Answer 5

thank you, i am adding around 150k rows into the temp table, so is it safe to say that it is better to do the insert first like in the third option?