我希望减少我的存储过程的执行时间。 [英] I want the decrease the execution time of my Stored Procedure.

问题描述

以下陈述用于我的存储过程。它花了很长时间执行我想减少我的执行时间。



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，

描述，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

#products

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


SELECT
ROW,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

推荐答案

检查此如何快速存储程序？ [ ^ ]

这篇关于我希望减少我的存储过程的执行时间。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！