我希望减少我的存储过程的执行时间。

@SourceId int， @SupplierNameId int SELECT @ProductId = Null， @CategoryId = Null， @SourceId = Null， @SupplierNameId = Null IF OBJECT_ID（' tempdb ..＃products'）IS NOT NULL DROP TABLE #products SELECT ROW， s.ProductId，ProductCode，Abbreviation，Name，BriefDescription， 描述，ImageSrc，s.Status，DisplayOrder，CreatedBy，s.UpdatedBy ，ViewedCnt，s.CategoryId， cast（0为十进制（18,2））'Price'，cast（0为十进制（18,2））'Discount'，cast（0为十进制） （18,2））'SalePrice'，''ProductAttrsCount'， convert（varchar（100），null，101）'Sale_Price_Start_Date'，convert（varc har（100），null，101）'discount_Start_Date'， convert（varchar（100），null，101）'Discount_End_Date'，cast（0为十进制（18,2））'Buying_Price' ，ISNULL（CONVERT（VARCHAR（10），UpdatedOn，111），CONVERT（VARCHAR（10），CreatedOn，111））'modifiedOn'， CONVERT（VARCHAR（10），CreatedOn，111） 'createdON'， Supplier_Id为SupplierId，Supplier_Category_ID AS SupplierCategoryId，Supplier_ProductID as SupplierProductId，Supplier_Name AS'SuiteName'，TaxableYN，Tax_Start_Date AS EffectiveFrom，Supplier_Product_Code， Supplier_Product_Name， Supplier_Category_Name， MarkupPercent，Packing_Type，Units_Per_CP，Size，Buying_Price_Effective_From AS'Buying_Price_Start_date'， FileName AS'Source'，Category 到#products FROM（ 选择 ROW_NUMBER（）OVER（按顺序排序时p.UpdatedOn不是NULL和p p.Upd，.createdON不是NULL atedOn 当p.UpdatedOn不为空时那么p.UpdatedOn 当p.UpdatedOn为NULL时那么p.createdON END DESC）AS ROW， p.ProductId，p.ProductCode，p。缩写，p.Name， c.Name Category1， p。[ BriefDescription]，p。[描述]，p.ImageSrc， p。[Status]，p.DisplayOrder，isnull（p.CreatedBy，0）as CreatedBy，isnull（p.UpdatedBy，0） as UpdatedBy， isnull（p.ViewedCnt，0）如ViewedCnt，p.CategoryId，p.UpdatedOn，p.CreatedOn， SS.Supplier_Id，SP.Supplier_Category_ID， SS.Supplier_ProductID，S.Supplier_Name， ISNULL（P.TaxableYN，'N'）AS'TaxableYN'，PD.Tax_Start_Date，SP.Supplier_Product_Code，SP.Supplier_Product_Name， SPC.Supplier_Category_Name， ISNULL（M arkupPercent，0.0）AS MarkupPercent，Packing_Type，Units_Per_CP， P.Size，spp.Buying_Price_Effective_From ，CASE WHEN p.FileId IS NULL那么'直接'ELSE FM.FileName结束为'FileName'， p.product_category_path为'类别' 来自 dbo.Products as p WITH（NOLOCK） 内连接dbo.Categories c WITH（NOLOCK）on p.CategoryId = c.CategoryId AND ISNULL（C.DeletedYN，0）= 0 内部联接dbo.SupplierProducts_To_SiteProducts SS WITH（NOLOCK）ON SS.Site_ProductID = P.ProductId 内部联接dbo.Supplier S WITH（NOLOCK）ON S.Supplier_ID = SS.Supplier_Id inner join dbo.supplier_products SP WITH（NOLOCK）ON SP.Supplier_Product_ID = ss.Supplier_ProductID inner join dbo.Supplier_Product_Categories SPC WITH（NOLOCK）ON SPC.Supplier_Category_ID = SP.Supplier_Category_ID 离开加入db o.Product_Tax_Details PD WITH（NOLOCK）ON PD.Product_ID = P.ProductId AND ISNULL（ActiveYN，0）= 1 left join dbo.Supplier_Product_Price spp WITH（NOLOCK）on spp.Supplier_Product_ID = ss。 Supplier_ProductID LEFT JOIN dbo.FileMaster AS FM WITH（NOLOCK）ON FM.FileId = P.FileId Where（@ProductId IS NULL或p.ProductId = @ProductId） 和（@CategoryId IS NULL或p.CategoryId = @CategoryId） 和（@SupplierNameId IS NULL或S.Supplier_ID = @SupplierNameId） 和（@SourceId IS NULL或FM.FileId = @SourceId） 和isnull（p.IsActive，0）= 1 AND ISNUll（P.DeletedYN，0）= 0 和isnull（p.status，0）= 1 ）AS S update temp set temp.Buying_Price = isnull（pp.Buying_Price，0） - ，temp.Discount = isnull（pp.discount，0） ，temp .SalePrice = isnull（pp.Sale_Price，0）， Sale_Price_Start_Date = pp.Sale_Price_Start_Date， discount_Start_Date = pp.Discount_Start_Date， Discount_End_Date = pp.Discount_End_Date， temp.Price = ISNULL（pp.sale_Price，0） 来自#products temp 加入dbo.Product_Price pp on pp.Product_Id = temp.ProductID - - 更新折扣 update temp set temp.discount = ISNULL（PP.DISCOUNT，0） FROM #products temp LEFT JOIN dbo。 PRODUCT_PRICE PP ON PP.PRODUCT_ID = temp.ProductID WHERE（DATEADD（D，0，DATEDIFF（D，0，ISNULL（PP.DISCOUNT_START_DATE，getdate（））））< = DATEADD（D ，0，DATEDIFF（D，0，getdate（））） AND DATEADD（D，0，DATEDIFF（D，0，ISNULL（PP.DISCOUNT_END_DATE，getdate（））））> = DATEADD（D，0，DATEDIFF（D，0，getdate（）））） - - - 更新销售价格 更新临时 设置temp.saleprice =（isnull（temp.SalePrice，0） - isnull（temp.discount，0）） FROM #products temp ------产品attr数量 UPDATE temp SET temp.ProductAttrsCount = PrdAttrCnt FROM #products temp INNER JOIN（选择ProductId，count（pa.ProductAttrId）为'PrdAttrCnt'来自dbo.ProductAttrs PA 加入dbo.ProductAttrsValues PAV on pa.ProductAttrId = pav.ProductAttrId 和pav.SelectedValue<>'' 和isnull（pa.DeletedYN，0）= 0 group by ProductId ）as t2 on t2.ProductId = temp。产品ID 在#products上创建索引idx_产品（productid，ProductCode，SupplierId，SupplierCategoryId，categoryid，Sale_Price_Start_Date，discount_Start_D ate） - 选择*来自#products - 按createdON，ISNULL排序（modifiedOn ，createdON）desc - drop table #products SELECT ROW，ProductId，ProductCode，Abbreviation，Name，BriefDescription， 描述，ImageSrc，Status，DisplayOrder，CreatedBy，UpdatedBy，ViewedCnt， CategoryId，Price，Discount，SalePrice ，ProductAttrsCount，Sale_Price_Start_Date， discount_Start_Date，Discount_End_Date，Buying_Price，modifiedOn，createdON， SupplierId，SupplierCategoryId，SupplierProductId，SupplierName，TaxableYN，EffectiveFrom， Supplier_Product_Code，Supplier_Product_Name，Supplier_Category_Name，MarkupPercent， Packing_Type，Units_Per_CP，Size，Buying_Price_Start_date，Source，Category FROM #productsThe below statements were used in my Stored Procedure. Its taking a long time for executing i want reduce my execution time .DECLARE @ProductId int , @CategoryId int , @SourceId int , @SupplierNameId int SELECT @ProductId =Null, @CategoryId =Null, @SourceId =Null, @SupplierNameId =Null IF OBJECT_ID('tempdb..#products') IS NOT NULL DROP TABLE #products SELECT ROW, s.ProductId, ProductCode, Abbreviation, Name , BriefDescription, Description, ImageSrc, s.Status, DisplayOrder, CreatedBy, s.UpdatedBy, ViewedCnt, s.CategoryId, cast(0 as decimal(18,2)) 'Price',cast(0 as decimal(18,2)) 'Discount',cast(0 as decimal(18,2)) 'SalePrice',0 'ProductAttrsCount', convert(varchar(100),null,101) 'Sale_Price_Start_Date',convert(varchar(100),null,101) 'discount_Start_Date', convert(varchar(100),null,101) 'Discount_End_Date',cast(0 as decimal(18,2)) 'Buying_Price',ISNULL(CONVERT(VARCHAR(10),UpdatedOn,111),CONVERT(VARCHAR(10),CreatedOn,111)) 'modifiedOn', CONVERT(VARCHAR(10),CreatedOn,111) 'createdON', Supplier_Id as SupplierId,Supplier_Category_ID AS SupplierCategoryId,Supplier_ProductID as SupplierProductId,Supplier_Name AS 'SupplierName',TaxableYN,Tax_Start_Date AS EffectiveFrom,Supplier_Product_Code, Supplier_Product_Name, Supplier_Category_Name, MarkupPercent,Packing_Type, Units_Per_CP, Size ,Buying_Price_Effective_From AS 'Buying_Price_Start_date', FileName AS 'Source',Category into #products FROM ( select ROW_NUMBER() OVER( Order BY case WHEN p.UpdatedOn IS NOT NULL AND p.createdON IS NOT NULL THEN p.UpdatedOn WHEN p.UpdatedOn IS NOT NULL THEN p.UpdatedOn WHEN p.UpdatedOn IS NULL THEN p.createdON END DESC) AS ROW, p.ProductId, p.ProductCode, p.Abbreviation, p.Name, c.Name Category1, p.[BriefDescription], p.[Description], p.ImageSrc, p.[Status], p.DisplayOrder, isnull(p.CreatedBy,0) as CreatedBy, isnull(p.UpdatedBy,0) as UpdatedBy, isnull(p.ViewedCnt,0) as ViewedCnt, p.CategoryId , p.UpdatedOn, p.CreatedOn, SS.Supplier_Id, SP.Supplier_Category_ID, SS.Supplier_ProductID, S.Supplier_Name, ISNULL(P.TaxableYN,'N') AS 'TaxableYN', PD.Tax_Start_Date, SP.Supplier_Product_Code, SP.Supplier_Product_Name, SPC.Supplier_Category_Name, ISNULL(MarkupPercent,0.0) AS MarkupPercent, Packing_Type, Units_Per_CP, P.Size , spp.Buying_Price_Effective_From ,CASE WHEN p.FileId IS NULL THEN 'Direct' ELSE FM.FileName END AS 'FileName', p.product_category_path as 'Category' from dbo.Products as p WITH(NOLOCK) inner join dbo.Categories c WITH(NOLOCK) on p.CategoryId=c.CategoryId AND ISNULL(C.DeletedYN,0) = 0 inner join dbo.SupplierProducts_To_SiteProducts SS WITH(NOLOCK) ON SS.Site_ProductID = P.ProductId inner join dbo.Supplier S WITH(NOLOCK) ON S.Supplier_ID = SS.Supplier_Id inner join dbo.supplier_products SP WITH(NOLOCK) ON SP.Supplier_Product_ID = ss.Supplier_ProductID inner join dbo.Supplier_Product_Categories SPC WITH(NOLOCK) ON SPC.Supplier_Category_ID = SP.Supplier_Category_ID left join dbo.Product_Tax_Details PD WITH(NOLOCK) ON PD.Product_ID = P.ProductId AND ISNULL(ActiveYN,0) = 1left join dbo.Supplier_Product_Price spp WITH(NOLOCK) on spp.Supplier_Product_ID = ss.Supplier_ProductIDLEFT JOIN dbo.FileMaster AS FM WITH(NOLOCK) ON FM.FileId = P.FileId Where (@ProductId IS NULL or p.ProductId = @ProductId) and (@CategoryId IS NULL or p.CategoryId = @CategoryId) and (@SupplierNameId IS NULL or S.Supplier_ID = @SupplierNameId) and (@SourceId IS NULL or FM.FileId = @SourceId) and isnull(p.IsActive,0) = 1 AND ISNUll(P.DeletedYN,0) = 0 and isnull(p.status,0)=1 ) AS S update temp set temp.Buying_Price = isnull(pp.Buying_Price,0) --, temp.Discount = isnull(pp.discount, 0) , temp.SalePrice = isnull(pp.Sale_Price,0), Sale_Price_Start_Date=pp.Sale_Price_Start_Date, discount_Start_Date=pp.Discount_Start_Date, Discount_End_Date=pp.Discount_End_Date, temp.Price=ISNULL(pp.sale_Price,0) from #products temp join dbo.Product_Price pp on pp.Product_Id = temp.ProductID-- -- update discount update temp set temp.discount = ISNULL(PP.DISCOUNT,0) FROM #products temp LEFT JOIN dbo.PRODUCT_PRICE PP ON PP.PRODUCT_ID = temp.ProductID WHERE ( DATEADD(D,0,DATEDIFF(D,0,ISNULL(PP.DISCOUNT_START_DATE,getdate()))) <= DATEADD(D,0,DATEDIFF(D,0,getdate())) AND DATEADD(D,0,DATEDIFF(D,0,ISNULL(PP.DISCOUNT_END_DATE,getdate()))) >= DATEADD(D,0,DATEDIFF(D,0,getdate())))-- -- update saleprice update temp set temp.saleprice = ( isnull(temp.SalePrice,0) - isnull(temp.discount, 0) ) FROM #products temp------ product attr count UPDATE temp SET temp.ProductAttrsCount = PrdAttrCnt FROM #products temp INNER JOIN (select ProductId, count(pa.ProductAttrId) as 'PrdAttrCnt' from dbo.ProductAttrs PA join dbo.ProductAttrsValues PAV on pa.ProductAttrId = pav.ProductAttrId and pav.SelectedValue <>'' and isnull(pa.DeletedYN,0) =0 group by ProductId ) as t2 on t2.ProductId = temp.ProductID CREATE index idx_Products on #products(productid,ProductCode,SupplierId,SupplierCategoryId,categoryid,Sale_Price_Start_Date,discount_Start_Date) --select * from #products -- order by createdON ,ISNULL(modifiedOn,createdON) desc --drop table #products SELECTROW,ProductId,ProductCode,Abbreviation,Name,BriefDescription,Description,ImageSrc,Status,DisplayOrder,CreatedBy,UpdatedBy,ViewedCnt,CategoryId,Price,Discount,SalePrice,ProductAttrsCount,Sale_Price_Start_Date,discount_Start_Date,Discount_End_Date,Buying_Price,modifiedOn,createdON,SupplierId,SupplierCategoryId,SupplierProductId,SupplierName,TaxableYN,EffectiveFrom,Supplier_Product_Code,Supplier_Product_Name,Supplier_Category_Name,MarkupPercent,Packing_Type,Units_Per_CP,Size,Buying_Price_Start_date,Source,Category FROM#products推荐答案检查此如何快速存储程序？ [ ^ ] 这篇关于我希望减少我的存储过程的执行时间。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！