I want the decrease the execution time of my Stored Procedure.

See more:

SQL-Server

SQL-Server-2008

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