T-SQL 插入 - 在表变量中选择非常慢 [英] T-SQL Insert - Select in Table variable is very slow
问题描述
我有一个表变量，我正在使用Insert Into - Select"语句在其中插入一些值.select是几个join的组合，单独执行时需要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
)
以下代码是我的问题陈述(插入--选择):
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屋！
