使用临时表与表变量会产生何种不同效果?

日期: 2010-11-09 作者:Denny Cherry翻译:曾少宁 来源:TechTarget中国 英文

我曾经对一个可用于从数据库删除数据的过程进行性能调优。我们每天都需要删除大量的数据,所以数据删除过程对于我们来说非常重要。随着客户群的增大,我们每天需要删除的数据量也在增长,同时删除数据所需要的时间也在增加。   由于删除数据需要花费大量的时间,所以我开始研究数据删除程序。

  我首先检查了实际删除语句。这些语句似乎没有问题,删除的操作都非常快(我们是按一批1000记录的方式删除数据的,以降低数据锁的时间)。所以,我开始检查查询(Select)所需要删除数据的代码。在执行计划中,似乎所有代码都没有问题。

  但是这一块代码的运行时间长达50分钟。从数据库查询返回1000条记录就需要这样长的时间……

我们一直都在努力坚持原创.......请不要一声不吭,就悄悄拿走。

我原创,你原创,我们的内容世界才会更加精彩!

【所有原创内容版权均属TechTarget,欢迎大家转发分享。但未经授权,严禁任何媒体(平面媒体、网络媒体、自媒体等)以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论,本站保留删除与本文无关和不雅评论的权力。

我曾经对一个可用于从数据库删除数据的过程进行性能调优。我们每天都需要删除大量的数据,所以数据删除过程对于我们来说非常重要。随着客户群的增大,我们每天需要删除的数据量也在增长,同时删除数据所需要的时间也在增加。

  由于删除数据需要花费大量的时间,所以我开始研究数据删除程序。

  我首先检查了实际删除语句。这些语句似乎没有问题,删除的操作都非常快(我们是按一批1000记录的方式删除数据的,以降低数据锁的时间)。所以,我开始检查查询(Select)所需要删除数据的代码。在执行计划中,似乎所有代码都没有问题。

  但是这一块代码的运行时间长达50分钟。从数据库查询返回1000条记录就需要这样长的时间是非常糟糕的。

  SQL:
  SELECT TOP (@BatchSize) a.PolicyIncidentId
  FROM PolicyIncident a WITH (NOLOCK)
  JOIN #ComputersToProcess ComputersToProcess ON a.ComputerId = ComputersToProcess.ComputerId
  WHERE CaptureTimestamp <ComputersToProcess.StartDeleteAt

  首先,我在@ComputersToProcess表上设置一个主键。这样就将表扫描转换成聚簇索引扫描(Clustered Index Scan),但是这对性能没有任何影响。

  下一步,我将表变量转换成一个临时表(不带主键)。这实际上并不会提升这个过程的速度,因为这里仍然没有数据统计。然而,执行计划的时间实际显示临时表中是没有统计值的。

  现在,我不希望在表上设置一个非聚簇索引而使表成为一个堆,而且非主键的聚簇索引是不会比主键更高效的,所以我将在表上设置了一个主键。虽然查询开销从2%增加到7%,但是实际的运行时间从50分钟降低到仅仅1秒钟。

  现在,我不需要再对这些过程作任何修改,而只需将表变量转换成临时表,然后添加一个主键,这样就能够将这个只有三行代码的查询的时间从一小时降低到一秒钟。如此小的修改就能实现这样明显的效果是很让人惊讶的。

  显然,这里并不是在修复任何问题。但是,在实际应用中,我的一个表变量(现在变成临时表)仅仅只有190k行记录,就已经是表变量所无法接受的了。要记住,SQL Server的表变量只有统计值,但是不管表变量实际上有多少数据,它假定每一个临时表只有一条记录。

翻译

曾少宁
曾少宁

TechTarget中国特约技术编辑,某高校计算机科学专业教师和网络实验室负责人,曾任职某网络国际厂商,关注数据中心、开发运维、数据库及软件开发技术。有多本关于思科数据中心和虚拟化技术的译著,如《思科绿色数据中心建设与管理》和《基于IP的能源管理》等。

相关推荐