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

i have table variable , inserting in values using "insert - select" statement. select combination of few joins, , when executed separately takes 3 seconds. problem whole code takes 3-4 minutes executed. wonder there particular reason this.

this 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 other table variable initialize data, , structure:

declare @temptable table (     protosurveyid int,     productid nvarchar(25) primary key ) 

and following code problem statement (insert - select):

insert @result (productid,productname,productcategoryid,productcategory,productsubcategoryid,productsubcategory,brandid,brand) select           products.productid  productid         ,products.productname  productname         ,productcategories.productcategoryid  productcategoryid         ,productcategories.productcategory   productcategory         ,productsubcategories.productsubcategoryid  productsubcategoryid         ,productsubcategories.productsubcategory  productsubcategory         ,brands.brandid brandid         ,brands.brand  brand  (         select                     cast(a.col001 tinyint) productcategoryid               ,cast(a.col002 tinyint) brandid               ,cast(a.col003 nvarchar(25)) productid               ,cast(a.col004 nvarchar(100)) productname               ,cast(a.col006 tinyint) productsubcategoryid               ,b.protosurveyid         datasetsmaterializeddatasqlvariant          inner join @temptable b          on b.productid=cast(a.col003 nvarchar(25))         datasetsmaterializedinternalrowsetid = 3  ) products       inner join (          select cast(a.col001 tinyint) brandid               , cast(a.col002 nvarchar(50)) brand          datasetsmaterializeddatasqlvariant          datasetsmaterializedinternalrowsetid = 1 )brands on products.brandid=brands.brandid inner join (          select cast(a.col001 tinyint) productcategoryid                 ,cast(a.col002 nvarchar(50)) productcategory          datasetsmaterializeddatasqlvariant          datasetsmaterializedinternalrowsetid = 2 ) productcategories on products.productcategoryid=productcategories.productcategoryid inner join (          select cast(a.col001 tinyint) productsubcategoryid               , cast(a.col002 nvarchar(50)) productsubcategory          datasetsmaterializeddatasqlvariant          datasetsmaterializedinternalrowsetid = 11 ) productsubcategories on products.productsubcategoryid=productsubcategories.productsubcategoryid 

as told before, if comment insert line query takes 3 seconds, otherwise - long time.

edit: here execution plan - of cost table scan, why takes many time when insert make, , happens quick without it?

the follow new inline function:

create function [dbo].[fn_xxcustom_retailaudits_getproductsforfilter] (     @securityobjectuserid bigint ) returns table return      cte(protosurveyid,productid)     (         select  distinct  cast(b.protosurveyid int)                          ,cast(a.col002 nvarchar(25)) productid         datasetsmaterializeddatasqlvariant         join surveyinstances b on a.col001=b.surveyinstanceid , cast(b.protosurveyid int) in (select protosurveyid dbo.fn_filter_getprotosurveysallowedshort(@securityobjectuserid, 'clientaccess',null))         datasetsmaterializedinternalrowsetid = 5     )     select               products.productid  productid             ,products.productname  productname             ,productcategories.productcategoryid  productcategoryid             ,productcategories.productcategory   productcategory             ,productsubcategories.productsubcategoryid  productsubcategoryid             ,productsubcategories.productsubcategory  productsubcategory             ,brands.brandid brandid             ,brands.brand  brand          (             select                         cast(a.col001 tinyint) productcategoryid                   ,cast(a.col002 tinyint) brandid                   ,cast(a.col003 nvarchar(25)) productid                   ,cast(a.col004 nvarchar(100)) productname                   ,cast(a.col006 tinyint) productsubcategoryid                   ,b.protosurveyid             cte b             inner join datasetsmaterializeddatasqlvariant              on b.productid=cast(a.col003 nvarchar(25))             datasetsmaterializedinternalrowsetid = 3      ) products           inner join     (              select cast(a.col001 tinyint) brandid                    ,cast(a.col002 nvarchar(50)) brand              datasetsmaterializeddatasqlvariant              datasetsmaterializedinternalrowsetid = 1     )brands on products.brandid=brands.brandid     inner join     (              select  cast(a.col001 tinyint) productcategoryid                     ,cast(a.col002 nvarchar(50)) productcategory              datasetsmaterializeddatasqlvariant              datasetsmaterializedinternalrowsetid = 2     ) productcategories on products.productcategoryid=productcategories.productcategoryid     inner join     (              select  cast(a.col001 tinyint) productsubcategoryid                     ,cast(a.col002 nvarchar(50)) productsubcategory              datasetsmaterializeddatasqlvariant              datasetsmaterializedinternalrowsetid = 11     ) productsubcategories on products.productsubcategoryid=productsubcategories.productsubcategoryid  go 

i runs again. ideas how optimize it?

queries insert table variables can't have parallel plan.

try using #temp table instead allow select parallelised.