Index on a Temp table conundrum

Question

Index on a Temp table conundrum

Viewing 8 posts - 16 through 22 (of 22 total)

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

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

Jack;

Well, I'll be! It works just as you said. That's awesome. Kind of befuddling but awesome none the less.

Now, where it appears I had no solution, I have two viable solutions and I know a little more about the arcania of SLQ server.

Maybe I'll be able to get a job doing this some day.. imagine :w00t:

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

Jack;

I do have a bit of a problem. When I use a separate create clustered index statement - I get an error (caused by the table hint of the update statement)

Index 'Answers_PK' on table '#Answers' (specified in the FROM clause) does not exist.

Is the only way to get around this to insert a "GO" after the create table statement so that the query parser actually sees the index in place? That would be a bummer because that means I'd have to move all of my variable declarations (which are neatly declared at the top of the script) into the body of the script. Ugh.

What I have noticed is that I can gain quite a bit of performance by creating an unindexed table, inserting the data and then creating the index - so I've moved the create index after the insert. That part is good.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Michael,

I don't know why you are getting the error, unless you changed the index name? Here is a feeble attempt to "duplicate" your code in a minimal manner that has worked as anticipated for me:

[font="Courier New"]DECLARE @Depth NUMERIC(6,2),

@PREV_UWI VARCHAR(100),

@PREV_EvalNum INT,

@PREV_Depth NUMERIC(6,2)

CREATE TABLE #temp

(

uwi VARCHAR(100),

evalnum INT,

depth numeric(6,2),

topDepth numeric(6,2)

)

INSERT INTO #temp

SELECT TOP 11000

sc1.name,

CONVERT(INT, sc1.id),

CONVERT(INT, sc1.xtype) + sc2.xtype,

NULL

FROM

MASTER.dbo.SysColumns sc1,

MASTER.dbo.SysColumns sc2

GROUP BY

sc1.name ,

CONVERT(INT, sc1.id),

CONVERT(INT, sc1.xtype) + sc2.xtype

CREATE UNIQUE CLUSTERED INDEX IX_test ON #temp(uwi, evalnum, depth)

UPDATE ANS

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

@PREV_UWI = UWI,

@PREV_EvalNum = EvalNum,

@PREV_Depth = Depth

FROM

#temp ANS WITH (TABLOCK, INDEX (IX_Test))

SELECT * FROM #temp

DROP TABLE #temp

[/font]

One minor note is that this has run on a SQL 2005 box, but is SQL 2000 compliant code. Don't know if SQL 2005 is handling the hint exactly the same way as 2000.

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 4

Jack;

I think it must have to do with the difference between SQL 2000 and 2005 because I copied you code and pasted it into a window and tried to run it and I got the same error as I got with my code.

Bummer.

Well, we'll be at 2005 some day :doze:

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 5

I think the only thing that needs to change is changing TABLOCK to TABLOCKX...

Please post your latest error...

--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 6

Hi Jeff.

For the sake of simplicity I used Jack's script which functionally does the same thing that I'm trying to do.

I substituted TABLOCK for TABLOCKX and still got;

Msg 308, Level 16, State 1, Line 30

Index 'IX_Test' on table '#temp' (specified in the FROM clause) does not exist.

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 7

Ah... that would be a correct error because the index does not exist at compile time and SQL Server 2000 is gonna complain about it.

There's no need to name the index on a temp table... Either that or put a "GO" in the script right after the CREATE INDEX...

Don't use the Index hint in the Query... it will be used anyway. If you want the warm fuzzies, put an ORDER BY on the same columns as the clustered index.

You could also create the index as a constraint in the table creation...

--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)

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

Michael (6/2/2008)
Hi Jeff.
For the sake of simplicity I used Jack's script which functionally does the same thing that I'm trying to do.
I substituted TABLOCK for TABLOCKX and still got;
Msg 308, Level 16, State 1, Line 30
Index 'IX_Test' on table '#temp' (specified in the FROM clause) does not exist.

That looks like the compiler error (smart enough to see that the table is being created - but not smart enough to figure out that the index will be there as well by the time you get there).

If the INDEX(1) hint works then that might be your best bet. Otherwise - wrapping that whole UPDATE statement (with the named index) into a dynamic call would also work (I just get really nervous around using the index numbers, in case someone "gets creative" somehow like adding another index with a unique constraint, etc...). Just forcing the lower scope will allow it to figure out that the index is in fact there.

----------------------------------------------------------------------------------
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?