Index for Temporary table in a Stored Procedure

Index for Temporary table in a Stored Procedure

Post by Steve Robinso » Thu, 12 Feb 1998 04:00:00



Valeria,

To allow the index to be used place your select statement within an exec eg

exec("select * from authors")
This will be evaluated at runtime at which point the index will be there and
all will be well

I hope this was of use to you.

Steve Robinson


>I have a stored procedure where I create a temp table and after I populate
>it I create the index. When I run my selects after the index is created,
SQL
>does a table scan. I try to force the index, but SQL returns a error
message
>saying that the index is not available. I also tried to break the code so
>that I would call a procedure after creating the index and it did not use
>the index. Another idea was to use dynamic select statements, but I cannot
>use it in all situations. If I run the same code through ISQL, SQL uses the
>index. How can I force SQL to use the index in the stored procedure?



 
 
 

Index for Temporary table in a Stored Procedure

Post by Valeria Rona » Fri, 13 Feb 1998 04:00:00


I have a stored procedure where I create a temp table and after I populate
it I create the index. When I run my selects after the index is created, SQL
does a table scan. I try to force the index, but SQL returns a error message
saying that the index is not available. I also tried to break the code so
that I would call a procedure after creating the index and it did not use
the index. Another idea was to use dynamic select statements, but I cannot
use it in all situations. If I run the same code through ISQL, SQL uses the
index. How can I force SQL to use the index in the stored procedure?



 
 
 

Index for Temporary table in a Stored Procedure

Post by Kalen Delane » Fri, 13 Feb 1998 04:00:00


You can create the temp table and index in one proc, and then call another
proc which accesses the table.

The optimizer will not choose to use an index created in the same proc as
the select, because the entire proc is optimized at the same time, and the
optimizer doesn't have any index details when optimizing the select
statements. In fact, as one of your error message suggests, it doesn't even
know the index is there.

The other alternative is dynamically executing the select, but if you can't
use it in all cases, I don't know of anything else.

HTH
--
Kalen Delaney
MCSE, SQL Server MCT, MVP


>I have a stored procedure where I create a temp table and after I populate
>it I create the index. When I run my selects after the index is created,
SQL
>does a table scan. I try to force the index, but SQL returns a error
message
>saying that the index is not available. I also tried to break the code so
>that I would call a procedure after creating the index and it did not use
>the index. Another idea was to use dynamic select statements, but I cannot
>use it in all situations. If I run the same code through ISQL, SQL uses the
>index. How can I force SQL to use the index in the stored procedure?



 
 
 

Index for Temporary table in a Stored Procedure

Post by Mike Siege » Fri, 13 Feb 1998 04:00:00


Use the 'with recompile' statement.  This will force your procedure to be
re-evaluated.

Mike


>I have a stored procedure where I create a temp table and after I populate
>it I create the index. When I run my selects after the index is created,
SQL
>does a table scan. I try to force the index, but SQL returns a error
message
>saying that the index is not available. I also tried to break the code so
>that I would call a procedure after creating the index and it did not use
>the index. Another idea was to use dynamic select statements, but I cannot
>use it in all situations. If I run the same code through ISQL, SQL uses the
>index. How can I force SQL to use the index in the stored procedure?



 
 
 

1. temporary tables in stored procedures/parameter views vs stored procedures

What is the cost of creating a cursor by
using a remote view (eg, a select statement eminating from the front end)
using a server based view ( the select statement is a view definition in
the database) or
using a stored procedure on the database  and called via SQLEXEC to create
the cursor?
I seem to have more success using temporary tables in stored procs rather
than views. I'm beginning to think its not a good idea to select into t1
for example, but to select into #t1, but it seems easier in sps than in
views. GO makes the temp tables go out of scope unless it's the last piece
of the script!
I also need to deal with the issue of parameter passing and outer joins.
These are specifiable in the front end, but I've found it more efficient to
write the sql than play with the view designer, and examine the sql at arms
length. Are sps the only vehicle that accept parameters?
I got here because the SQL I needed had to do a distinct on a text type, so
I was backed into creating the result set in stages ( hence the temp
tables) where I create the substrate that will accommodate the join I neeed
to include a text field.
Thanks all for your pateince and help
Drew

2. Q: ODBC alternative to DbLib's ::dbdead

3. Stored procedures and indexes on temporary tables

4. Time calculations

5. Storing the result of a stored procedure call in a temporary table

6. Server: Msg 7619, Level 16, Part II

7. Stored Procedure temporary table failing - HELP!!!!

8. Internet Tools for FoxPro 2.6

9. alter temporary table within a dynamically built stored procedure

10. Results from stored procedure to temporary table?

11. Stored procedure -- referencing temporary tables in

12. temporary tables vs. stored procedures

13. Cannot use ADO's MoveLast at temporary table of stored procedure