SP_PREPEXEC

• 89netanel

  SSCrazy

  Points: 2130

  More actions

  June 27, 2017 at 12:19 am

  #326961

  Hello all,
  i have a query that runs many times in and its execution plan and "thinks" that there is a table above 500M rows.
  this is a table that i know that it contain 20,000 rows maximum (there is a process that fill the table and a process that delete rows but it can be more than 20,000 rows).
  the query use sp_prepexec which not familiar to me.
  you can see below screenshot of the execution plan that the estimated rows is more than 500,000,000 but the actual rows is 16,800.
  how can i hint the query to be more accurate and to understand that the estimated rows will be less than 25,000 rows?This is the query:

  declare @p1 int
  set @p1=4587

  exec sp_prepexec @p1 output,N'@P0 bigint',N'select distinct product1_.ID as ID1_44_0_, productide2_.ProductIdentifierID as ProductI1_46_1_, resourcede3_.ID as ID1_48_2_, ingestionf4_.ID as ID1_12_3_, product1_.CreateDt as CreateDt2_44_0_, product1_.UpdateDt as UpdateDt3_44_0_, product1_.OPTLOCK as OPTLOCK4_44_0_, product1_.editionNumber as editionN5_44_0_, product1_.editionVersionNumber as editionV6_44_0_, product1_.productComposition as productC7_44_0_, product1_.productForm as productF8_44_0_, product1_.externalIdentifier as external9_44_0_, product1_.notificationType as notific10_44_0_, product1_.ProviderId as Provide18_44_0_, product1_.publishingStatus as publish11_44_0_, product1_.rowSalesRightType as rowSale12_44_0_, product1_.recordSourceName as recordS13_44_0_, product1_.recordSourceType as recordS14_44_0_, product1_.status as status15_44_0_, product1_.UpdateContentDt as UpdateC16_44_0_, product1_.updateHash as updateH17_44_0_, productide2_.idTypeName as idTypeNa2_46_1_, productide2_.idValue as idValue3_46_1_, productide2_.Type as Type4_46_1_, productide2_.ProductId as ProductI5_44_0__, productide2_.ProductIdentifierID as ProductI1_46_0__, resourcede3_.CreateDt as CreateDt2_48_2_, resourcede3_.UpdateDt as UpdateDt3_48_2_, resourcede3_.OPTLOCK as OPTLOCK4_48_2_, resourcede3_.mandatory as mandator5_48_2_, resourcede3_.satisfied as satisfie6_48_2_, resourcede3_.type as type7_48_2_, resourcede3_1_.[Description] as Descript1_49_2_, resourcede3_2_.FileID as FileID2_50_2_, case when resourcede3_1_.ProductResourceDependencyID is not null then 1 when resourcede3_2_.ProductResourceDependencyID is not null then 2 when resourcede3_.ID is not null then 0 end as clazz_2_, resourcede3_.ProductId as ProductI8_44_1__, resourcede3_.ID as ID1_48_1__, ingestionf4_.CreateDt as CreateDt2_12_3_, ingestionf4_.UpdateDt as UpdateDt3_12_3_, ingestionf4_.OPTLOCK as OPTLOCK4_12_3_, ingestionf4_.FileID as FileID5_12_3_, ingestionf4_.ProductId as ProductI6_44_2__, ingestionf4_.ID as ID1_12_2__ from M_Temp_External_Identifier tempextern0_ cross join M_Product product1_ left outer join M_ProductIdentifier productide2_ on product1_.ID=productide2_.ProductId left outer join M_ProductResourceDependency resourcede3_ on product1_.ID=resourcede3_.ProductId left outer join M_ProductResourceDescriptiveDependency resourcede3_1_ on resourcede3_.ID=resourcede3_1_.ProductResourceDependencyID left outer join M_ProductResourceFileDependency resourcede3_2_ on resourcede3_.ID=resourcede3_2_.ProductResourceDependencyID left outer join IngestionFileProduct ingestionf4_ on product1_.ID=ingestionf4_.ProductId where tempextern0_.externalIdentifier=product1_.externalIdentifier and product1_.ProviderId=@P0        ',54

  select @p1

  

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  June 27, 2017 at 2:39 am

  #1948311

  Quick look at this query will show that it uses the anti-pattern of feeding a high cardinality result set into the distinct operator, hence the high estimation in the plan. Suggest you improve the query as it is the problem.
  😎

  The query
  SELECT DISTINCT product1_.id       AS ID1_44_0_,
      productide2_.productidentifierid AS ProductI1_46_1_,
      resourcede3_.id      AS ID1_48_2_,
      ingestionf4_.id      AS ID1_12_3_,
      product1_.createdt     AS CreateDt2_44_0_,
      product1_.updatedt     AS UpdateDt3_44_0_,
      product1_.optlock      AS OPTLOCK4_44_0_,
      product1_.editionnumber    AS editionN5_44_0_,
      product1_.editionversionnumber AS editionV6_44_0_,
      product1_.productcomposition  AS productC7_44_0_,
      product1_.productform    AS productF8_44_0_,
      product1_.externalidentifier  AS external9_44_0_,
      product1_.notificationtype   AS notific10_44_0_,
      product1_.providerid     AS Provide18_44_0_,
      product1_.publishingstatus   AS publish11_44_0_,
      product1_.rowsalesrighttype  AS rowSale12_44_0_,
      product1_.recordsourcename   AS recordS13_44_0_,
      product1_.recordsourcetype   AS recordS14_44_0_,
      product1_.status      AS status15_44_0_,
      product1_.updatecontentdt   AS UpdateC16_44_0_,
      product1_.updatehash     AS updateH17_44_0_,
      productide2_.idtypename    AS idTypeNa2_46_1_,
      productide2_.idvalue     AS idValue3_46_1_,
      productide2_.type      AS Type4_46_1_,
      productide2_.productid    AS ProductI5_44_0__,
      productide2_.productidentifierid AS ProductI1_46_0__,
      resourcede3_.createdt    AS CreateDt2_48_2_,
      resourcede3_.updatedt    AS UpdateDt3_48_2_,
      resourcede3_.optlock     AS OPTLOCK4_48_2_,
      resourcede3_.mandatory    AS mandator5_48_2_,
      resourcede3_.satisfied    AS satisfie6_48_2_,
      resourcede3_.type      AS type7_48_2_,
      resourcede3_1_.[description]  AS Descript1_49_2_,
      resourcede3_2_.fileid    AS FileID2_50_2_,
      CASE
      WHEN resourcede3_1_.productresourcedependencyid IS NOT NULL
      THEN 1
      WHEN resourcede3_2_.productresourcedependencyid IS NOT NULL
      THEN 2
      WHEN resourcede3_.id IS NOT NULL THEN 0
      END          AS clazz_2_,
      resourcede3_.productid    AS ProductI8_44_1__,
      resourcede3_.id      AS ID1_48_1__,
      ingestionf4_.createdt    AS CreateDt2_12_3_,
      ingestionf4_.updatedt    AS UpdateDt3_12_3_,
      ingestionf4_.optlock     AS OPTLOCK4_12_3_,
      ingestionf4_.fileid     AS FileID5_12_3_,
      ingestionf4_.productid    AS ProductI6_44_2__,
      ingestionf4_.id      AS ID1_12_2__
FROM m_temp_external_identifier tempextern0_
   CROSS JOIN m_product product1_
   LEFT OUTER JOIN m_productidentifier productide2_
       ON product1_.id = productide2_.productid
   LEFT OUTER JOIN m_productresourcedependency resourcede3_
       ON product1_.id = resourcede3_.productid
   LEFT OUTER JOIN m_productresourcedescriptivedependency resourcede3_1_
       ON resourcede3_.id =
        resourcede3_1_.productresourcedependencyid
   LEFT OUTER JOIN m_productresourcefiledependency resourcede3_2_
       ON resourcede3_.id =
        resourcede3_2_.productresourcedependencyid
   LEFT OUTER JOIN ingestionfileproduct ingestionf4_
       ON product1_.id = ingestionf4_.productid
WHERE tempextern0_.externalidentifier = product1_.externalidentifier
   AND product1_.providerid = @P0

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  June 28, 2017 at 10:46 am

  #1948589

  Update statistics.