Creating indexes on temp tables in stored procedures

Help,

I have a complex stored procedure (>1000 lines) that uses multiple temp tables with thousands of rows. These temp tables are joined together, and selected from many times.

I tried to improve the performance of the procedure by createing the recommended indexes on my temp tables, but the query plan ignores the indexes and continues to use table scans.

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Developer Edition (Build 2600: Service Pack 2)

Can you provide some samples of the queries your issuing against these tables?
And maybe some examples of the tables...

|||

The temp tables are used in a cursor (trust me, there's no other way). So these tables of upwards of 200,000 rows each need to be joined, to pull out just a few rows at a time. The tables are fairly simple, around 10 columns each. My issue is that SQL Server doesn't pay attention to indexes created on temp tables because the execution plan is predetermined (I think) and it doesn't take into account an index that doesn't exist at compile-time.

I may have solved my issue by creating another stored procedure that I call from the first. this sub-stored procedure creates indexes on the temp tables (that's all it does) and it looks like the execution plan is no longer doing table scans to join two tables together... it appears to be using the indexes I've created. If you have an easier way to get SQL to use a just-created index, I'm all ears.

FYI: pet peeve of mine

your = something you own (posessive)

you're = conjunction form of "you are"

|||

First off, if you are going to punish people for grammer (yes, that is grammar then you are going to be sorely dissappointed. First off no spell check. Second, no pay. Third, well, come on it is just a bit of help.

Second: the biggest pet peeve of them all around here is not posting your code and DDL so we can look at what you are doing.

Third: "The temp tables are used in a cursor (trust me, there's no other way)." This is rarely true. Almost any cursor can be dealt with in set based code. Some order based accumulations are faster in cursors (or so I have heard , but I haven't written a cursor for a non-system function in years.

You could try adding a hint to your query to force index use. That might work. Try the WITH RECOMPILE hint on the proc too. If nothing else, you can try declaring the cursor in a dynamic SQL call:

declare @.cursorDeclare varchar(max)

set @.cursorDeclare = 'declare bob cursor global for select ''hi'' as hi

open bob'

exec (@.cursorDeclare)

fetch next from bob

That might do the trick. Or, if there are just a few rows to be returned, this might work to spool the dynamic query into a temp table:

create table #tempper

(

value varchar(10)

)

insert into #tempper

exec ('select ''value''')

select *

from #tempper

|||Here, here to the cursor advice; Cursors Are Loathesome.