T-SQL Insert - Select in Table variable is very slow

Question

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.

This is the my table variable declaration:

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

As I told before, if I comment the insert line the query takes 3 seconds, otherwise - a very long time.

EDIT: 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?

Answer 1

Queries that insert into table variables can't have a parallel plan.

Try using a #temp table instead to allow the SELECT to be parallelised.