Temp Table Indexing??

15 Sep 2006 12:07 AM

Dan

I have a stored procedure that does heavy query to get a dataset. That
dataset is then used in several other queries and joined to other physical
tables in the database. When I use a temp table to store the dataset, it is
very slow when joining in the subsequent queries. I have determined that this
is because of the lack of indexes on the temp table.

I have come up with a bit of a hack to improve performance. But I would like
to find a better way to do this:

My solution was to create a physical table, that has the proper indexes on
it. Instead of using a temp table I now save my results to the physical table
with a GUID to identify all the rows in my current dataset. When I am done
with the stored proc, I delete all the rows with that GUID. Making this
change made the whole SPROC perform MUCH faster.

In essence, this method acts like a temp table, but I get the benefit of
indexes. The problem is that it kills disk I/O since I am writing to a
physical table. I would prefer to find a way to do this all in memory.

Is there a way to utilize indexes in temp tables or table variables that I
am not aware of?

Are there any other possible solutions?

15 Sep 2006 12:19 AM

Cowboy (Gregory A. Beamer)

Yes, you can index temp tables, at least in SQL Server 2005. I recently did
a dependency check and looked a profiler. Many parts of the query index the
temp tables created. I have not tried it personally.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com/

*************************************************
Think Outside the Box!
*************************************************

Show quoteHide quote

"Dan" <D**@discussions.microsoft.com> wrote in message
news:CF699FB3-8067-458F-AD95-8E66F95835CE@microsoft.com...
>I have a stored procedure that does heavy query to get a dataset. That
> dataset is then used in several other queries and joined to other physical
> tables in the database. When I use a temp table to store the dataset, it
> is
> very slow when joining in the subsequent queries. I have determined that
> this
> is because of the lack of indexes on the temp table.
>
> I have come up with a bit of a hack to improve performance. But I would
> like
> to find a better way to do this:
>
> My solution was to create a physical table, that has the proper indexes on
> it. Instead of using a temp table I now save my results to the physical
> table
> with a GUID to identify all the rows in my current dataset. When I am done
> with the stored proc, I delete all the rows with that GUID. Making this
> change made the whole SPROC perform MUCH faster.
>
> In essence, this method acts like a temp table, but I get the benefit of
> indexes. The problem is that it kills disk I/O since I am writing to a
> physical table. I would prefer to find a way to do this all in memory.
>
> Is there a way to utilize indexes in temp tables or table variables that I
> am not aware of?
>
> Are there any other possible solutions?

15 Sep 2006 2:08 AM

Alexander Kuznetsov

Cowboy (Gregory A. Beamer) wrote:

> Yes, you can index temp tables, at least in SQL Server 2005.

You could do it as early as in 6.5 just as well...

15 Sep 2006 12:33 AM

Kalen Delaney

Hi Dan

There are no restrictions on indexing temp tables. However, neither temp
tables nor table variables are in-memory structures.
Take a look at this KB article:
http://support.microsoft.com/kb/305977/en-us

--
HTH
Kalen Delaney, SQL Server MVP

Show quoteHide quote

"Dan" <D**@discussions.microsoft.com> wrote in message
news:CF699FB3-8067-458F-AD95-8E66F95835CE@microsoft.com...
>I have a stored procedure that does heavy query to get a dataset. That
> dataset is then used in several other queries and joined to other physical
> tables in the database. When I use a temp table to store the dataset, it
> is
> very slow when joining in the subsequent queries. I have determined that
> this
> is because of the lack of indexes on the temp table.
>
> I have come up with a bit of a hack to improve performance. But I would
> like
> to find a better way to do this:
>
> My solution was to create a physical table, that has the proper indexes on
> it. Instead of using a temp table I now save my results to the physical
> table
> with a GUID to identify all the rows in my current dataset. When I am done
> with the stored proc, I delete all the rows with that GUID. Making this
> change made the whole SPROC perform MUCH faster.
>
> In essence, this method acts like a temp table, but I get the benefit of
> indexes. The problem is that it kills disk I/O since I am writing to a
> physical table. I would prefer to find a way to do this all in memory.
>
> Is there a way to utilize indexes in temp tables or table variables that I
> am not aware of?
>
> Are there any other possible solutions?