我可以在存储过程中动态创建的临时表 (#temp) 上创建索引吗?

发布于 2024-08-14 08:07:26 字数 422 浏览 3 评论 0原文

我正在存储过程中创建临时表 (#temp_table)。这是一个包含大量数据的巨大表。然后我在存储过程中创建索引,因为它需要更快地查询临时表。但是当我执行存储过程时,没有使用索引。执行存储过程时未创建索引。

伪代码

CREATE PROC abcdefg
AS
...
SELECT col_a, col_b, col_c....    
  INTO #temp_table
  FROM .....
  WHERE ....
...
CREATE INDEX abc_idx ON #temp_table (col_a)
...
SELECT col_a FROM #temp_table WITH (INDEX (abc_idx))
...
GO

当我尝试执行存储过程时,它无法识别索引。我该如何解决这个问题?

I am creating temp tables (#temp_table) in my stored procedure. It is a huge table with large data. Then I am creating a index in the storeed procedure as it is required for faster query to the temp table. But when I execute the stored procedure, the index is not used. The index is not being created when the stored procedure is executed.

Pseudo code

CREATE PROC abcdefg
AS
...
SELECT col_a, col_b, col_c....    
  INTO #temp_table
  FROM .....
  WHERE ....
...
CREATE INDEX abc_idx ON #temp_table (col_a)
...
SELECT col_a FROM #temp_table WITH (INDEX (abc_idx))
...
GO

When I try to execute the stored proc, it is not recognizing the index. How can I fix this problem?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

梦魇绽荼蘼 2024-08-21 08:07:26

通常会发生这种情况,因为临时表的访问计划是在索引存在之前生成的。

幸运的是,您可以使用表级约束来解决这个问题。由于支持 UNIQUE 和 PRIMARY KEY 约束的索引与临时表同时定义,因此优化器将始终能够使用这些索引。

参考:优化临时表的性能/索引

This usually happens because the access plans for the temp tables have been generated before the indexes ever existed.

Fortunately, you can use table-level constraints to get around this problem. Since the indexes to support UNIQUE and PRIMARY KEY constraints are defined at the same time as the temp table, the optimizer will always be able to use these indexes.

Reference: Optimizing Performance / Indexes on Temp Tables

瀞厅☆埖开 2024-08-21 08:07:26

我使用 Sql Server 2005 尝试了您建议的代码,

ALTER PROCEDURE Test
AS
BEGIN
    CREATE TABLE #Test (
            ID INT,
            Code VARCHAR(20)
    )
    CREATE INDEX test_ind ON #Test (Code)

    INSERT INTO #Test (ID,Code) SELECT ID, Code FROM MyTable

    SELECT Code
    FROM    #Test WITH(INDEX(test_ind))

    DROP TABLE #Test
END

当运行

EXEC Test

包含实际执行计划的命令时,它确实显示索引已被使用。

I tried the code you suggested using Sql Server 2005

ALTER PROCEDURE Test
AS
BEGIN
    CREATE TABLE #Test (
            ID INT,
            Code VARCHAR(20)
    )
    CREATE INDEX test_ind ON #Test (Code)

    INSERT INTO #Test (ID,Code) SELECT ID, Code FROM MyTable

    SELECT Code
    FROM    #Test WITH(INDEX(test_ind))

    DROP TABLE #Test
END

When running the

EXEC Test

Command with Include Actual Execution Plan, it does show that the index was used.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文