logo标签列表

能否为临时表添加索引？创建 #t 和声明 @t 有什么区别？

sqlsql-servert-sqlindexingtemp-tables
30
我需要执行一个非常复杂的查询。在某个时候，这个查询必须与一个无法索引的视图进行连接。不幸的是，这个视图也是一个连接大表的复杂视图。
视图的输出可以简化为以下内容：
PID (int), Kind (int), Date (date), D1,D2..DN

PID、日期和种类字段不是唯一的（可能有多行具有相同的pid、kind、date组合），但这些字段将用于像这样的联接

left join ComplexView mkcs on mkcs.PID=q4.PersonID and mkcs.Date=q4.date and mkcs.Kind=1
left join ComplexView mkcl on mkcl.PID=q4.PersonID and mkcl.Date=q4.date and mkcl.Kind=2
left join ComplexView mkco on mkco.PID=q4.PersonID and mkco.Date=q4.date and mkco.Kind=3

现在，如果我只是这样做，执行查询需要很长时间，因为复杂视图被运行了三次，我认为，它的大量行中只有一些实际使用（比如，40000个中只有2000个被使用）。

我所做的是声明@temptable，并插入select * from ComplexView where Date...到@temptable中 - 每次查询时我只选择我要使用的行，并将其加入@temptable中，然后再连接这个@temptable。这显著减少了执行时间。

但是，我注意到，如果我在我的数据库中创建一个表，并在PID、Kind、Date上添加一个聚集索引（非唯一聚集），然后从这个表中获取数据，然后执行delete * from这个表并从复杂视图中插入数据需要几秒钟（3或4秒），然后在我的查询中使用这个表（左连接它三次）将查询时间缩短了一半，从1分钟缩短到30秒！

那么，我的问题是，首先 - 是否可以在声明的@temptables上创建索引。 然后 - 我看到人们谈论“create #temptable”语法。也许这就是我需要的？我在哪里可以阅读关于declare @temptable和create #temptable之间区别的内容？对于像我这样的查询应该使用什么？（这个查询是用于MS Reporting Services报告，如果有关系的话）。

5个回答
23

#tablename是一个物理表，存储在tempdb中，当创建它的连接关闭时，服务器会自动删除它。而@tablename是存储在内存中的表，其生命周期与创建它的批处理/过程相同，就像本地变量一样。

你只能向#temp表添加非主键索引。

create table #blah (fld int)
create nonclustered index idx on #blah (fld)
3
如果两个人同时运行使用临时表的查询，那么它是否意味着他们将访问同一个#tablename但不同@tablenames，因此在脚本中使用#tablename进行临时查询是不安全的？换句话说，当两个查询同时运行，并且每个查询都具有相同的#temptable标识符的创建语句时会发生什么？它们会各自得到自己的#temptable还是都访问同一张表？ - Istrebitel
啊，我在下面的链接中找到了答案。 - Istrebitel
5
@DanzaiVer，这个链接来自一个已删除的答案；http://www.sqlteam.com/article/optimizing-performance-indexes-on-temp-tables - Alex K.
表变量不一定“存储在内存中”。它们的处理方式与#temp表相同：它们将从内存开始，但随着它们的增长而被拖到tempdb中的物理磁盘上。主要区别在于语法和范围（表变量的范围限定于创建它们的批处理/函数/过程：#temp表限定于会话）。 - Curt
7

这不是一个完整的答案，但是 #table 会创建一个临时表，你需要删除它，否则它会在你的数据库中保留。而 @table 是一个表变量，它不会比你的脚本更长时间地存在。

此外，我认为这篇文章将回答你问题的另一部分。

在表变量上创建索引

18
临时表会在服务器连接断开后消失，仅在该连接范围内存在。并不是说丢弃不再需要的东西是一种不好的习惯，只是并非必须的。 - JeffO
有趣，我不知道那个。感谢澄清。 - Brian Dishaw
2
实际上这是必需的。例如，当我在SQL管理工具中运行查询时，它不会关闭会话，因此创建语句会出现错误，指示对象已经存在！因此，我认为最好手动删除它。网络上的文章也建议这样做。 - Istrebitel
@JeffO 为什么这是一种不好的做法？我认为相反的情况是正确的 - 即使临时表在连接断开时会被删除，自己删除它也是一个好习惯。 - Apostrofix
1
抱歉我的措辞有些令人困惑，有太多的否定词了。这是一个好习惯，只不过并非必须。 - JeffO
显示剩余3条评论
4
4
@tableName 语法是表变量，功能相对有限。具体语法请参考 DECLARE @local_variable 的相关文档。表变量可以间接地通过在列上指定 PRIMARY KEY 和 UNIQUE 约束来创建索引，但只能用于唯一的数据列。因此，如果你需要对数据列建立索引并且这些数据列唯一，那么可以使用这种方法。详见这个回答。这种方法对于小数量的数据行可能足够使用，但是如果表变量中包含了数百或数千行数据，而没有为其创建索引，则优化器通常会将表变量视为仅包含一行数据，从而导致查询计划较差。 #tableName 语法是本地范围的临时表，可以使用 SELECT…INTO #tableName 或 CREATE TABLE #tableName 语法进行创建。这些表的作用域比变量的范围更加复杂。如果在存储过程中创建了 CREATE TABLE #tableName，则该存储过程中所有对 #tableName 的引用都将引用该表。如果仅在存储过程中引用 #tableName（而没有创建它），则会查找 调用方 的作用域。因此，您可以在一个存储过程中创建 #tableName，然后调用另一个存储过程，在那个存储过程中读取/更新 #tableName。但是，一旦创建了 #tableName 的存储过程运行完成，该表将被 SQL Server 自动取消引用并清理。因此，除非有存储过程需要循环或长时间运行，否则没有必要手动清理这些表。
你可以像永久表一样在临时表上定义复杂的索引。因此，如果需要对重复值进行索引列，而又不能使用 UNIQUE，则应采用这种方法。您甚至不必担心索引名称冲突的问题。如果在多个会话中运行类似于 CREATE INDEX my_index ON #tableName(MyColumn) 这样的语句，每个会话都创建自己的名为 #tableName 的表，SQL Server 将执行一些魔法，以便全局标识符 my_index 的重用不会导致冲突。详见这里。

此外，临时表将自动构建统计信息等内容，就像普通表一样。查询优化器将意识到临时表可能不止有 1 行数据，这本身可以导致比表变量更高的性能增益。当然，这也是一小部分开销。尽管这种开销很可能值得，如果您的查询运行时间超过一秒钟，则不会注意到。

4
扩展Alex K.的答案，您可以在临时表上创建PRIMARY KEY。
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
 DROP TABLE #tempTable

CREATE TABLE #tempTable 
(
   Id INT PRIMARY KEY
  ,Value NVARCHAR(128)
)

INSERT INTO #tempTable
VALUES 
     (1, 'first value')
    ,(3, 'second value')
    -- will cause Violation of PRIMARY KEY constraint 'PK__#tempTab__3214EC071AE8C88D'. Cannot insert duplicate key in object 'dbo.#tempTable'. The duplicate key value is (1).
    --,(1, 'first value one more time')


SELECT  * FROM #tempTable
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接