T-SQL插入-在表中选择变量非常慢 [英] T-SQL Insert - Select in Table variable is very slow
问题描述
我有一个表变量，并使用插入到-选择"语句在其中插入一些值.该选择是少量联接的组合，当单独执行该选择时，将花费3秒钟.问题是整个代码需要3-4分钟才能执行.我想知道这是否有特定原因.
I have a table variable and I am inserting in it some values using the "Insert Into - Select" statement. The select is a combination of few joins, and when it is executed separately it takes 3 seconds. The problem is that the whole code takes 3-4 minutes to executed. I wonder is there a particular reason for this.
这是我的表变量声明:
DECLARE @Result TABLE
(
ProductID NVARCHAR(25) PRIMARY KEY
,ProductName NVARCHAR(100)
,ProductCategoryID TINYINT
,ProductCategory NVARCHAR(50)
,ProductSubCategoryID TINYINT
,ProductSubCategory NVARCHAR(50)
,BrandID TINYINT
,Brand NVARCHAR(50)
)
我还有另一个表变量，我用一些数据对其进行了初始化，这就是它的结构:
I have an other table variable which I initialize with some data, and this is its structure:
DECLARE @TempTable TABLE
(
ProtoSurveyID INT,
ProductID NVARCHAR(25) PRIMARY KEY
)
，以下代码是我的问题陈述(插入-select):
and the following code is my problem statement (insert into - select):
INSERT INTO @Result (ProductID,ProductName,ProductCategoryID,ProductCategory,ProductSubCategoryID,ProductSubCategory,BrandID,Brand)
SELECT
Products.ProductID AS ProductID
,Products.ProductName AS ProductName
,ProductCategories.ProductCategoryID AS ProductCategoryID
,ProductCategories.ProductCategory AS ProductCategory
,ProductSubCategories.ProductSubCategoryID AS ProductSubCategoryID
,ProductSubCategories.ProductSubCategory AS ProductSubCategory
,Brands.BrandID AS BrandID
,Brands.Brand AS Brand
FROM
(
SELECT
CAST(A.Col001 AS tinyint) AS ProductCategoryID
,CAST(A.Col002 AS tinyint) AS BrandID
,CAST(A.Col003 AS nvarchar(25)) AS ProductID
,CAST(A.Col004 AS nvarchar(100)) AS ProductName
,CAST(A.Col006 As tinyint) AS ProductSubCategoryID
,B.ProtoSurveyID
FROM DataSetsMaterializedDataSqlvariant A
INNER JOIN @TempTable B
ON B.ProductID=CAST(A.Col003 AS nvarchar(25))
WHERE DataSetsMaterializedInternalRowsetID = 3
) Products
INNER JOIN
(
SELECT CAST(A.Col001 AS tinyint) AS BrandID
, CAST(A.Col002 AS nvarchar(50)) AS Brand
FROM DataSetsMaterializedDataSqlvariant A
WHERE DataSetsMaterializedInternalRowsetID = 1
)Brands On Products.BrandID=Brands.BrandID
INNER JOIN
(
SELECT CAST(A.Col001 AS tinyint) AS ProductCategoryID
,CAST(A.Col002 AS nvarchar(50)) AS ProductCategory
FROM DataSetsMaterializedDataSqlvariant A
WHERE DataSetsMaterializedInternalRowsetID = 2
) ProductCategories On Products.ProductCategoryID=ProductCategories.ProductCategoryID
INNER JOIN
(
SELECT CAST(A.Col001 AS tinyint) AS ProductSubCategoryID
, CAST(A.Col002 AS nvarchar(50)) AS ProductSubCategory
FROM DataSetsMaterializedDataSqlvariant A
WHERE DataSetsMaterializedInternalRowsetID = 11
) ProductSubCategories on Products.ProductSubCategoryID=ProductSubCategories.ProductSubCategoryID
正如我之前所说，如果我对插入行进行注释，则查询将花费3秒钟，否则-很长的时间.
As I told before, if I comment the insert line the query takes 3 seconds, otherwise - a very long time.
这是我的执行计划-大部分成本是表扫描，但是为什么在进行插入时花那么多时间，而在没有插入时却很快发生了?
Here is my execution plan - most of the cost is table scan, but why it takes so many time when insert is make, and happens to quick without it?
以下是我的新内联函数:
The follow is my new inline function:
CREATE FUNCTION [dbo].[fn_XxCustom_RetailAudits_GetProductsForFilter]
(
@SecurityObjectUserID BIGINT
)
RETURNS TABLE
AS
RETURN
WITH CTE(ProtoSurveyID,ProductID) AS
(
SELECT DISTINCT CAST(B.ProtoSurveyID AS INT)
,CAST(A.Col002 AS NVARCHAR(25)) AS ProductID
FROM DataSetsMaterializedDataSqlvariant A
JOIN SurveyInstances B ON A.Col001=B.SurveyInstanceID AND CAST(B.ProtoSurveyID AS INT) IN (SELECT ProtoSurveyID FROM dbo.fn_Filter_GetProtoSurveysAllowedShort(@SecurityObjectUserID, 'CLIENTACCESS',NULL))
WHERE DataSetsMaterializedInternalRowsetID = 5
)
SELECT
Products.ProductID AS ProductID
,Products.ProductName AS ProductName
,ProductCategories.ProductCategoryID AS ProductCategoryID
,ProductCategories.ProductCategory AS ProductCategory
,ProductSubCategories.ProductSubCategoryID AS ProductSubCategoryID
,ProductSubCategories.ProductSubCategory AS ProductSubCategory
,Brands.BrandID AS BrandID
,Brands.Brand AS Brand
FROM
(
SELECT
CAST(A.Col001 AS tinyint) AS ProductCategoryID
,CAST(A.Col002 AS tinyint) AS BrandID
,CAST(A.Col003 AS nvarchar(25)) AS ProductID
,CAST(A.Col004 AS nvarchar(100)) AS ProductName
,CAST(A.Col006 As tinyint) AS ProductSubCategoryID
,B.ProtoSurveyID
FROM CTE B
INNER JOIN DataSetsMaterializedDataSqlvariant A
ON B.ProductID=CAST(A.Col003 AS nvarchar(25))
WHERE DataSetsMaterializedInternalRowsetID = 3
) Products
INNER JOIN
(
SELECT CAST(A.Col001 AS tinyint) AS BrandID
,CAST(A.Col002 AS nvarchar(50)) AS Brand
FROM DataSetsMaterializedDataSqlvariant A
WHERE DataSetsMaterializedInternalRowsetID = 1
)Brands On Products.BrandID=Brands.BrandID
INNER JOIN
(
SELECT CAST(A.Col001 AS tinyint) AS ProductCategoryID
,CAST(A.Col002 AS nvarchar(50)) AS ProductCategory
FROM DataSetsMaterializedDataSqlvariant A
WHERE DataSetsMaterializedInternalRowsetID = 2
) ProductCategories On Products.ProductCategoryID=ProductCategories.ProductCategoryID
INNER JOIN
(
SELECT CAST(A.Col001 AS tinyint) AS ProductSubCategoryID
,CAST(A.Col002 AS nvarchar(50)) AS ProductSubCategory
FROM DataSetsMaterializedDataSqlvariant A
WHERE DataSetsMaterializedInternalRowsetID = 11
) ProductSubCategories on Products.ProductSubCategoryID=ProductSubCategories.ProductSubCategoryID
GO
我又慢跑了.有什么想法如何优化它吗?
I runs slowly again. Any ideas how to optimize it?
推荐答案
插入表变量中的查询不能有并行计划.
Queries that insert into table variables can't have a parallel plan.
尝试使用#temp表代替，以允许SELECT并行化.
Try using a #temp table instead to allow the SELECT to be parallelised.
这篇关于T-SQL插入-在表中选择变量非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
