Index on a Temp table conundrum

Question

Post reply

Index on a Temp table conundrum

CanuckBuck

Hall of Fame

Points: 3915
More actions
May 30, 2008 at 11:05 am

#186313

one of values (the one that I'm relying on) of a #temp table is that it is local to the process that is running it. However, I need to put a clustered index on my temp table (so that my process will complete in something shorter than a human lifetime) which I have to explicitly name.
Here's my problem.
If I'm running the same process in two different windows - even though the temp table is local to the process the index isn't
Does anyone have any ideas about how to get around this? Is it possible to name an index as a #temp_Index or something similar so that the index also is local to the process that creates it?
Thanks in advance for and thoughts you have.

Viewing 15 posts - 1 through 15 (of 22 total)

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

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 1

How are you creating the temp table? What kind of index are you creating?

If you do the following, you shouldn't have any problems:

Create Table #t1 (

id int primary key clustered

,column1 char(10));

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

Jeff solution will work if you do not have any duplicates in the column where you want the unique index. I have never seen the issue you are describing. I just ran this in one session:

Create Table #temp

(test int)

Create Clustered Index IX_test on #temp(test)

Select * from tempdb.dbo.sysindexes where name = 'IX_TEST'

WAITFOR Delay '00:00:10' -- gives me 10 seconds to run the other query

Drop table #temp

and then this in another session:

Create Table #temp

(test int)

Create Clustered Index IX_test on #temp(test)

Select * from tempdb.dbo.sysindexes where name = 'IX_TEST'

Drop table #temp

Both run successfully and in the second session the Select * from tempdb.dbo.sysindexes where name = 'IX_TEST' returns 2 rows.

Can you post the code or a variation of it and how it is being called?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

How about making sure the name is unique?

Just create the table, then look up its object_ID(). Doing that will return the #Temp table you created, even if there were multiple #temp tables built within other sessions. Add the object_ID to the index name.

declare @t_id bigint

create table #temp (rid int identity(1,1))

set @t_id=object_id('tempdb..#temp');

declare @sql varchar(400)

set @sql='create index ix_temp'+cast(@t_id as varchar(20))+' on #temp(rid)'

Exec(@SQL)

So - the index name is now predictable.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Sergiy SSC Guru Points: 110206 More actions · Answer 4

You may define constraints without naming it.

Create Table #t1 (

id int NOT NULL ,

column1 char(10) NULL,

UNIQUE clustered (column1)

)

_____________
Code for TallyGenerator

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 5

Thanks for all of the feedback. I realize I should have provided a bit more detail. After I posted, I learned, by reading the help files that;

- I can create a clustered index on a temp table without explicitly naming it.

- I can look up the index name in sysindexes

The reason I need to know the name is so that I can use it as a hint in a subsequent operation.

The operation I'm performing updates values in the table based on variables saved from the previous row. It relies on the clustered index specified as a hint to get the order correct (this is a VERY efficient technique I learned on this forum. It can be used in place of a cursor. I love this technique. I use it a lot.).

I can look up the index name and store it in a variable. The problem is that I can't use the variable as a table hint.

I think that the only way that I'd be able to make that work is to build the query as a concatenated string and then execute it. Yech!

However, it occurs to me that since the clustered index is the only index on the table, perhaps I don't even need to use a table hint (perhaps the update statement will use it by default). I'm going to explore this option.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Michael (5/30/2008)
Thanks for all of the feedback. I realize I should have provided a bit more detail. After I posted, I learned, by reading the help files that;
- I can create a clustered index on a temp table without explicitly naming it.
- I can look up the index name in sysindexes
The reason I need to know the name is so that I can use it as a hint in a subsequent operation.
The operation I'm performing updates values in the table based on variables saved from the previous row. It relies on the clustered index specified as a hint to get the order correct (this is a VERY efficient technique I learned on this forum. It can be used in place of a cursor. I love this technique. I use it a lot.).
I can look up the index name and store it in a variable. The problem is that I can't use the variable as a table hint.
I think that the only way that I'd be able to make that work is to build the query as a concatenated string and then execute it. Yech!
However, it occurs to me that since the clustered index is the only index on the table, perhaps I don't even need to use a table hint (perhaps the update statement will use it by default). I'm going to explore this option.

Don't take my word for it....but you need the index hint or your update will give you really f'ed up results.....It will at best be inconsistent... Sounds like you're heading for Dynamic SQL Land....:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 7

Jack. Thanks for your post.

I'm at home for the weekend now. I'll post some code on Monday.

The essence of it though is I'm testing for the exitance of the table, dropping it if it exists and recreating it. The definition of the table includes the constraint clause with an explicitly named index.

If that portion of the script is run in one SSMS window and the window remains open (so the temp table and explicitly named index still exist), and the same script is run in a second window (simulating two users running the process concurrently) When the second one tries to create the index I get an error telling me it already exists.

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 8

Matt. Thanks for the idea. I think it may work. I'll try it on Monday.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 9

I think you can use the index id instead of the name in your table hint. Since you will only have a single index on this table - INDEX=1 should work (or INDEX(1) on SQL Server 2005).

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

Just because a proc may run more than once concurrently, I try to never name constraints on temp tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 11

For those interested in the code I'm working with...

The data represents measurements of reservoir attributes from oil (bitumen actually) wells. Each depth down the well bore has a set of attribute measurements.

The essence of it is that my source data table is a "hyper normalized" key/value pairs table which I have to pivot. If I'm retrieving all of the pertinent data from all of the wells in the database the base table result set it's 34 million rows which I pivot down to 9 million!

The next step is to perform a number of calculations on the pivoted table. This is where I use the index hint and hence where I need to know the name of the index. The reason for the IF OBJECT_ID() statement at the top of the table create statement is that the user may repeatedly run the script with different data retrieval parameters.

Jack; If you could run this code you'd see that if a second person were to run the same code during the execution time of the first run that when the code got to the create table statement and the explicitly named index, it would generate an error.

Matt; Providing you're right about needing to use the table hint even if I only have one index, then I think you're right, I'm headed for dynamic SQL land :crying:

DECLARE

@Depth NUMERIC(6,2)

,@AVGMBit NUMERIC(5,4)

,@AVGPHIE NUMERIC(5,4)

,@AVGSwE NUMERIC(5,4)

,@AVGVsh NUMERIC(5,4)

,@PREV_UWI VARCHAR(13)

,@PREV_EvalNum INT

,@PREV_Depth NUMERIC(6,2)

,@PREV_MBit NUMERIC(4,3)

,@PREV_PHIE NUMERIC(4,3)

,@PREV_SwE NUMERIC(4,3)

,@PREV_Vsh NUMERIC(4,3)

,@PREV_AVGMBit NUMERIC(5,4)

,@PREV_Assemble AS VARCHAR(1)

,@GroupNum INT

IF OBJECT_ID(N'TempDB..#Answers',N'U') IS NOT NULL DROP TABLE #Answers

CREATE TABLE #Answers (

UWI VARCHAR(13) NOT NULL

,EvalNum INT NOT NULL

,Depth NUMERIC(6,2) NOT NULL

,MBit NUMERIC(4,3)

,PHIE NUMERIC(4,3)

,SwE NUMERIC(4,3)

,Vsh NUMERIC(4,3)

,TopDepth NUMERIC(6,2)

,BaseDepth NUMERIC(6,2)

,AVGMBit NUMERIC(5,4)

,AVGPHIE NUMERIC(5,4)

,AVGSwE NUMERIC(5,4)

,AVGVsh NUMERIC(5,4)

,OilCol NUMERIC(5,4)

,Length NUMERIC(6,2)

,GroupNum INT

CONSTRAINT Answers_PK PRIMARY KEY CLUSTERED (UWI, EvalNum, Depth))

INSERT INTO #Answers (UWI, EvalNum, Depth, MBit, PHIE, SwE, Vsh, BaseDepth)

SELECT

*

,Depth AS BaseDepth

FROM dbo.Answers_V

WHERE

UWI IN (SELECT * FROM @Process_Scope_Table)

--UWI IN (SELECT distinct TOP 1000 Resource_Entity_Id FROM Resource_Data_Profile WHERE Resource_Data_Type = 'LogAnalPropPt')

OPTION (MAXDOP 0)

UPDATE ANS SET

@Depth = TopDepth = CASE WHEN @PREV_UWI = UWI AND @PREV_EvalNum = EvalNum THEN @PREV_Depth ELSE NULL END

,@AVGMBit = AVGMBit = CASE WHEN @PREV_UWI = UWI AND @PREV_EvalNum = EvalNum THEN (@PREV_MBit + MBit)/2 ELSE NULL END

,@AVGPHIE = AVGPHIE = CASE WHEN @PREV_UWI = UWI AND @PREV_EvalNum = EvalNum THEN (@PREV_PHIE + PHIE)/2 ELSE NULL END

,@AVGSwE = AVGSwE = CASE WHEN @PREV_UWI = UWI AND @PREV_EvalNum = EvalNum THEN (@PREV_SwE + SwE)/2 ELSE NULL END

,@AVGVsh = AVGVsh = CASE WHEN @PREV_UWI = UWI AND @PREV_EvalNum = EvalNum THEN (@PREV_Vsh + Vsh)/2 ELSE NULL END

,@GroupNum = GroupNum = CASE WHEN @PREV_UWI = UWI AND @PREV_EvalNum = EvalNum THEN

CASE WHEN (@PREV_AVGMBit >= @MBit_CutOff AND @AVGMBit >= @MBit_CutOff)

OR (@PREV_AVGMBit < @MBit_CutOff AND @AVGMBit < @MBit_CutOff) THEN @GroupNum

ELSE @groupNum + 1

END

ELSE 1

END

,Length = @PREV_Depth - @Depth

,OilCol = (1 - @AVGSwE) * @AVGPHIE * (@PREV_Depth - @Depth)

,@PREV_UWI = UWI

,@PREV_EvalNum = EvalNum

,@PREV_Depth = Depth

,@PREV_MBit = MBit

,@PREV_PHIE = PHIE

,@PREV_SwE = SwE

,@PREV_Vsh = Vsh

,@PREV_AVGMBit = @AVGMBit

FROM #Answers ANS WITH (TABLOCK, INDEX (Answers_PK))

OPTION (MAXDOP 0)

Jack Corbett SSC Guru Points: 184393 More actions · Answer 12

Michael,

I don't know why SQL Server treats them differently, but changing my code to use a specifically named primary key constraint does cause the error, but using the Create Unique Clustered Index statement does not cause an error, and functionally they are the same thing. They both enforce uniqueness and order. Why not try using the Create Clustered Index statement?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 13

That's very interesting. I'll try it.

Stay tuned... 🙂

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 14

Thanks for this Jeffrey!

I'm using SQL 2000 right now. If I understand what you're saying then my update statement should look like this;