|
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 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 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
|
|||||||||||||||||||||||
