tsql – T-SQL插入 – 在表中选择变量非常慢

前端之家收集整理的这篇文章主要介绍了tsql – T-SQL插入 – 在表中选择变量非常慢前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有一个表变量,我使用“插入 – 选择”语句插入一些值. select是几个连接的组合,当它单独执行时需要3秒.问题是整个代码需要3-4分钟才能执行.我想知道这有什么特别的原因.

这是我的表变量声明:

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)
)

我有一个其他表变量,我用一些数据初始化,这是它的结构:

DECLARE @TempTable TABLE
(
    ProtoSurveyID INT,ProductID NVARCHAR(25) PRIMARY KEY
)

以下代码是我的问题陈述(插入 – 选择):

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秒,否则 – 很长一段时间.

编辑:这是我的执行计划 – 大部分成本是表扫描,但为什么在插入生成时需要这么多时间,如果没有它就会快速发生?

以下是我的新内联函数

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,Brands.Brand  AS Brand
    FROM 
    (
            SELECT     
                   CAST(A.Col001 AS tinyint) AS ProductCategoryID,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

我又慢慢跑了.任何想法如何优化它?

解决方法

插入表变量的查询不能具有并行计划.

尝试使用#temp表来允许并行化SELECT.

原文链接:https://www.f2er.com/mssql/83276.html

猜你在找的MsSQL相关文章