表变量导致查询性能下降？ [英] Table variable causing slow performance on query?

问题描述

我有一个存储过程，我将在其中为用户生成记录。但最近用户反馈说记录生成很慢，我们决定微调存储过程本身。在我的存储过程中，我有一些SELECT语句，我将使用表值函数（TVF）加入永久表，我的DBA反馈我们可以通过用表变量替换TVF来进一步提高性能。请参阅下面的更改前后示例：

  SELECT  
 c.CustomerName 
，c.CustomerAddress 
，c.CustomerOrderNo 
  FROM  
 dbo.Customer  AS  c 
  INNER   JOIN  dbo.utf_CustomerDetailInfoStatus（'  ACTIVE'） AS  cdi 
  ON  c.CustomerStatusId = cdi.CustomerStatusId 
 
 
  SELECT  
 c.CustomerName 
，c.CustomerAddress 
，c.CustomerOrderNo 
  FROM  
 dbo.Customer  AS  c 
  INNER   JOIN   @ CustomerDetailInfoStatus   AS  cdi 
  ON  c.CustomerStatusId = cdi.CustomerStatusId 

上面的例子只是一个简单的模拟来显示变化。我已经用表变量替换了5个TVF，但经过一些测试后，我发现性能已经从大约20秒到6分钟的执行持续时间进一步恶化（两者都返回了10k的记录）！我将表变量更改回TVF，它返回到大约20秒。但我的DBA坚持认为，通过用表变量替换TVF，其他因素如表锁定，I / O尖峰可以减少，但通过我的测试，结果不是我所期待的。

有没有人对此有所了解？



我的尝试：



1.完成TVF对表变量的一些研究，但没有一个主题与我的案例有关。

解决方案

< pre lang =SQL> - 如果您没有适当的客户索引，TVF我帮助较少。

CREATE INDEX Customer_index（CustomerStatusId）INCLUDE（客户名称） ，CustomerAddress，CustomerOrderNo）;

SELECT
c.CustomerName
，c.CustomerAddress
，c.CustomerOrderNo
FROM
dbo.Customer AS c
CROSS APPLY dbo.utf_CustomerDetailInfoStatus（' ACTIVE'） AS cdi
WHERE
c.CustomerStatusId = cdi.CustomerStatusId OPTION （FAST 10 ）;

- 也尝试这样

SELECT
c.CustomerName
，c.CustomerAddress
，c.CustomerOrderNo
FROM
dbo.Customer AS c WHERE EXISTS （
SELECT 1 FROM dbo.utf_CustomerDetailInfoStatus（' ACTIVE'） AS cdi
WHERE
c.CustomerStatusId = cdi.CustomerStatusId
）;

Hi, I have a stored procedure in which i will generate records for users. But recently users feedback that the records generation are slow and we have decided to fine tuning the stored procedure itself. In my stored procedure, i have some SELECT statements in which i will join permanent table with table valued function(TVF) and my DBA feedback we can further improve the performance simply by replacing the TVF with table variable. Please see below for example of before and after changes:

SELECT
	 c.CustomerName
	,c.CustomerAddress
	,c.CustomerOrderNo
FROM
	dbo.Customer AS c
	INNER JOIN dbo.utf_CustomerDetailInfoStatus('ACTIVE') AS cdi
		ON c.CustomerStatusId = cdi.CustomerStatusId


SELECT
	 c.CustomerName
	,c.CustomerAddress
	,c.CustomerOrderNo
FROM
	dbo.Customer AS c
	INNER JOIN @CustomerDetailInfoStatus AS cdi
		ON c.CustomerStatusId = cdi.CustomerStatusId

The above example is just a simple mock up to show the changes. There are 5 TVF that I have replaced with table variables but after some testing, i found the performance has deteriorate further from around 20 seconds to 6 minutes of execution duration(both returned 10k of records)! I change the table variables back into TVF and it returned to around 20 seconds. But my DBA insists that by replacing the TVF with table variables, other factors such as table locking, I/O spiking can be reduced but through testing from my end, the result is not what i have been expecting.
Does anyone has knowledge on this?

What I have tried:

1. Done some study on TVF vs Table Variable but none of the topics is somewhere related to my case here.

解决方案

--if you don't have proper index on Customer,TVF i'm help less.

CREATE INDEX Customer_index(CustomerStatusId) INCLUDE(CustomerName,CustomerAddress,CustomerOrderNo);

SELECT
	 c.CustomerName
	,c.CustomerAddress
	,c.CustomerOrderNo
FROM
	dbo.Customer AS c 
	  CROSS APPLY dbo.utf_CustomerDetailInfoStatus('ACTIVE') AS cdi
		WHERE
             c.CustomerStatusId = cdi.CustomerStatusId OPTION(FAST 10);

 --try like this too

SELECT
	 c.CustomerName
	,c.CustomerAddress
	,c.CustomerOrderNo
FROM
	dbo.Customer AS c   WHERE EXISTS(
  SELECT 1 FROM  dbo.utf_CustomerDetailInfoStatus('ACTIVE') AS cdi
		WHERE
                   c.CustomerStatusId = cdi.CustomerStatusId 
                                         );

这篇关于表变量导致查询性能下降？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！