|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Temp Table Indexing??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? 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. -- Show quoteHide quoteGregory A. Beamer MVP; MCP: +I, SE, SD, DBA http://gregorybeamer.spaces.live.com/ ************************************************* Think Outside the Box! ************************************************* "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? 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...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 -- Show quoteHide quoteHTH Kalen Delaney, SQL Server MVP "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?  
Other interesting topics
Update Statement
Date Range SQL query Problem with repeated use of temp tables xp_cmdshell - works with string in one format but not in another - Query help Need help with self-join Case-sensitivity and LIKE - not working in 2000? done in a single statement Are operations in a stored procedure treated as a transaction |
|||||||||||||||||||||||
