index on temporary table

index on temporary table

Post by Yuan, Pin » Thu, 29 Apr 1999 04:00:00



Hi:

We use temp table that can be potentially large, thus it impacts
performance.  The question is: can we somehow create index on temp table
and be picked up?  We tried different ways.  What happens is the code
compiles with "create index..." but it does not get picked up.  If we
try to use it as index hints, for example, (index = pk), we get an error
during run time, saying the index does not exist.  Anyone out there has
new ideas?

Thanks,

Ping

 
 
 

index on temporary table

Post by Tony Rogerso » Fri, 30 Apr 1999 04:00:00


Yuan,

Basically create the temporaray table and index and then any subsequent
access to the temporaray table that you require the index you must
dynamically execute the sql, this will cause a compile etc... of that
specific bit of sql thereby picking up the index...

create index idx1 on #temp

exec ( 'select * from #temp ( index = idx1 ) where mycol = 1' )

--
Tony Rogerson MCP,SQL Server MVP
Torver Computer Consultants Ltd.


>Hi:

>We use temp table that can be potentially large, thus it impacts
>performance.  The question is: can we somehow create index on temp table
>and be picked up?  We tried different ways.  What happens is the code
>compiles with "create index..." but it does not get picked up.  If we
>try to use it as index hints, for example, (index = pk), we get an error
>during run time, saying the index does not exist.  Anyone out there has
>new ideas?

>Thanks,

>Ping


 
 
 

index on temporary table

Post by Neil Pik » Fri, 30 Apr 1999 04:00:00


Ping,

 Use SQL 7 - this will use the index ok.

Quote:> We use temp table that can be potentially large, thus it impacts
> performance.  The question is: can we somehow create index on temp table
> and be picked up?  We tried different ways.  What happens is the code
> compiles with "create index..." but it does not get picked up.  If we
> try to use it as index hints, for example, (index = pk), we get an error
> during run time, saying the index does not exist.  Anyone out there has
> new ideas?

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)
 For SQL FAQ entries see
 http://go.compuserve.com/sqlserver (library 1) - latest stuff is always here
 www.ntfaq.com/sql.html
 http://www.swynk.com/faq/sql/sqlserverfaq.asp
 
 
 

index on temporary table

Post by Neil Pik » Sat, 15 May 1999 04:00:00


Ken,

Q.  Why do I get a "variable is not declared" inside my EXEC statement?
(v1.0  1999.05.14)

A.  This is because a variable is local to a batch of SQL - normally batches
are split up with GO statements.  However an EXEC is a separate batch and so it
cannot see any local variables already created.

You must put the variable declarations you need inside the EXEC statement.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)
 For SQL FAQ entries see
 http://go.compuserve.com/sqlserver (library 1) - latest stuff is always here
 www.ntfaq.com/sql.html
 http://www.swynk.com/faq/sql/sqlserverfaq.asp

 
 
 

1. Using Index on Temporary Table

Preecha ,

I dont know where the rest of this thread is but I am guessing that your
temp tables either dont exist or are empty when your stored procedure is
created.  If this is so then the reason why the indexes are not used is
because the execution plan is worked out when the procedure is created and
so no indexes or data exists.  If you are creating the index inside of the
procedure aswell SQL is just not clever enough to work out that it can use
that one UNLESS  you place the select statement inside of an exec("")
statement.  This will cause it to be evaluated at actual run-time meaning
you loose some of the performance from the procedure BUT it will see the
indexes then.

I hope this was of use to you.

Steve Robinson

2. web databases for classified advertising

3. Can't reference index on temporary table

4. SQL Server vs Oracle???

5. Index for Temporary table in a Stored Procedure

6. Sequences and MS-Access

7. Revise question on Creating an Index on temporary table

8. SP that sends email from ????

9. Problem creating Index for Temporary Table

10. Creating Index in Temporary table

11. Index on temporary tables

12. help Creating Index on Temporary Tables

13. Create Index for temporary table