Sometimes when I use temp tables in stored procedures I put indices on them
in the hope that they improve performance. My boss says that the indices do
not work.

I'm pretty sure that I sometimes see improvement. Especially if I create the
tables in one proc and call another that uses them.

Comments please.

Wayne

On Mon, 01 Aug 2005 22:35:51 -0400, Wayne Happ wrote:

>
> Sometimes when I use temp tables in stored procedures I put indices on
> them in the hope that they improve performance. My boss says that the
> indices do not work.
>
> I'm pretty sure that I sometimes see improvement. Especially if I create
> the tables in one proc and call another that uses them.

I believe that in relatively recent versions of ASE the indexes that you
create on temp tables (even in the same batch/proc) will be used by
queries. You can always check this with a showplan output...


Michael
--
Michael Peppler [TeamSybase] mpeppler@peppler.org - http://www.peppler.org/
Sybase DBA/Developer
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html

There is of course a play off between the time taken to create your
index and the lost in scanning the temp table, depending on how large
the resultset is.

Michael Peppler wrote:
> On Mon, 01 Aug 2005 22:35:51 -0400, Wayne Happ wrote:
>
>
>>Sometimes when I use temp tables in stored procedures I put indices on
>>them in the hope that they improve performance. My boss says that the
>>indices do not work.
>>
>>I'm pretty sure that I sometimes see improvement. Especially if I create
>>the tables in one proc and call another that uses them.
>
>
> I believe that in relatively recent versions of ASE the indexes that you
> create on temp tables (even in the same batch/proc) will be used by
> queries. You can always check this with a showplan output...
>
>
> Michael