Speed up my stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Speed up my stored procedure

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-27 : 08:44:35

This my stored procedure..
I need to speed up it (Better to eliminate cursor)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[Sp_PriceReport_TwoGap]
@usname varchar(100)
-- @ToDate datetime,
-- @MachineNumber varchar(100)
as

DECLARE @MAX int

declare @mobid numeric
DECLARE C1 CURSOR
FOR
select MOBID from tbl_product where Client_Name=@usname
-- where MachineNumber=@MachineNumber and
-- Spares_Date between @FromDate and @ToDate
-- order by(Spares_Date)

OPEN C1
FETCH NEXT FROM C1
INTO @mobid

WHILE @@FETCH_STATUS=0
BEGIN

declare @product_mpn varchar(100)
declare @product_desc varchar(70)
-- for base price
declare @mybase_price numeric(18,2)
declare @base_price_gap numeric(18,2)
declare @minid int
declare @maxid int
declare @low_price numeric(18,2)
declare @low_merchant varchar(100)
declare @high_price numeric(18,2)
declare @high_merchant varchar(100)
-- for bot line price
declare @bot_price numeric(18,2)
declare @bot_price_gap numeric(18,2)
declare @Botminid int
declare @Botlow_price numeric(18,2)
-- declare @Botmaxid int
-- declare @Botlow_merchant varchar(100)
-- declare @Bothigh_price numeric(18,2)
-- declare @Bothigh_merchant varchar(100)

set @product_mpn=(select MPN_SKU from tbl_product where MOBID=@mobid and Client_Name=@usname)

set @product_desc=(select Product_Name from tbl_product where MOBID=@mobid and Client_Name=@usname)

set @mybase_price=(select isnull(Price,0) from tbl_merchant where MOBID=@mobid and Merchant=@usname)

set @bot_price=(select isnull(Bottomline_Price,0) from tbl_merchant where MOBID=@mobid and Merchant=@usname)

------------Bottom line Price Gap
set @Botminid=(select top 1 id from tbl_merchant where MOBID=@mobid and
Bottomline_Price=(select isnull(min(Bottomline_Price),0) from tbl_merchant where MOBID=@mobid ))

set @Botlow_price=(select isnull(Bottomline_Price,0) from tbl_merchant where id=@Botminid)

set @bot_price_gap=@bot_price-@Botlow_price

-- set @Botlow_merchant=(select Merchant from tbl_merchant where id=@Botminid)

-- set @Botmaxid=(select top 1 id from tbl_merchant where MOBID=@mobid and
-- Bottomline_Price=(select isnull(max(Bottomline_Price),0) from tbl_merchant where MOBID=@Botmaxid ))
--
-- set @Bothigh_price=(select isnull(Bottomline_Price,0) from tbl_merchant where id=@Botmaxid)
--
-- set @Bothigh_merchant=(select Merchant from tbl_merchant where id=@Botmaxid)

------------Base Price Gap
set @minid=(select top 1 id from tbl_merchant where MOBID=@mobid and
Price=(select isnull(min(Price),0) from tbl_merchant where MOBID=@mobid))

set @low_price=(select isnull(Price,0) from tbl_merchant where id=@minid)

set @base_price_gap=@mybase_price-@low_price

set @low_merchant=(select Merchant from tbl_merchant where id=@minid)

set @maxid=(select top 1 id from tbl_merchant where MOBID=@mobid and
Price=(select isnull(max(Price),0) from tbl_merchant where MOBID=@mobid))

set @high_price=(select isnull(Price,0) from tbl_merchant where id=@maxid)

set @high_merchant=(select Merchant from tbl_merchant where id=@maxid)

declare @tblPrice_TwoGap_temp table
(
product_mpn varchar(100),
product_desc varchar(300),
mybase_price numeric(18, 2),
base_price_gap numeric(18, 2),
mybot_price numeric(18, 2),
bot_price_gap numeric(18, 2),
low_price numeric(18, 2),
low_merchant varchar(300),
high_price numeric(18, 2),
high_merchant varchar(300)
)
insert into @tblPrice_TwoGap_temp
select @product_mpn,@product_desc,@mybase_price,@base_price_gap,@bot_price,@bot_price_gap,
@low_price,@low_merchant,@high_price,@high_merchant

FETCH NEXT FROM C1
INTO @mobid
END
CLOSE C1
DEALLOCATE C1

begin
select product_mpn as ProductMPN,product_desc as Description,
mybase_price as MyBasePrice,base_price_gap as BasePriceGap,
mybot_price as MyBottPrice,bot_price_gap as BottPriceGap,
low_price as LowPrice,low_merchant as LowMerchant,
high_price as HighPrice,high_merchant as HighMerchant from @tblPrice_TwoGap_temp
end

-----------------------------------------------------------------------------------------

Thanks in advance...

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-27 : 09:24:08

Is there any alternative way for cursor

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-27 : 09:44:13

I guess you learned nothing?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=149262

N 56°04'39.26"
E 12°55'05.63"

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-27 : 09:53:27

I got confused in this section so only i asked for this

MAX(CASE WHEN low = 1 THEN Merchant ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN Bottomline_Price ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN Merchant ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN Bottomline_Price ELSE NULL END) AS HighPrice,
MAX(CASE WHEN My = 1 THEN Bottomline_Price ELSE NULL END) AS Bottomprice

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-27 : 09:55:33

In this SP i need to calculate one step

set @bot_price_gap=@bot_price-@low_price

how to add it in this loop

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-27 : 09:57:42

[code]MAX(CASE WHEN low = 1 THEN Merchant ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN Bottomline_Price ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN Merchant ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN Bottomline_Price ELSE NULL END) AS HighPrice,
MAX(CASE WHEN My = 1 THEN Bottomline_Price ELSE NULL END) AS Bottomprice,
MAX(CASE WHEN My = 1 THEN Bottomline_Price ELSE NULL END) - MAX(CASE WHEN low = 1 THEN Bottomline_Price ELSE NULL END) AS BotPriceGap[/code]

N 56°04'39.26"
E 12°55'05.63"

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-27 : 10:12:12

thank you sir ..I understanded about this ...

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-03 : 08:17:52

SELECT MobID,

MAX(CASE WHEN low = 1 THEN Merchant ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN Price ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN Merchant ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN Price ELSE NULL END) AS HighPrice,
MAX(CASE WHEN My = 1 THEN Price ELSE NULL END)-MAX(CASE WHEN low = 1 THEN Price ELSE NULL END) AS BasepriceGap,
MAX(CASE WHEN My = 1 THEN Price ELSE NULL END) AS Myprice,
MAX(CASE WHEN My = 1 THEN Price ELSE NULL END) AS Baseprice,
MAX(CASE WHEN My = 1 THEN mobid ELSE NULL END) AS ID,
MAX(CASE WHEN My = 1 THEN Bottomline_Price ELSE NULL END) as BotPrice,
MAX(CASE WHEN My = 1 THEN Bottomline_Price ELSE NULL END) - MAX(CASE WHEN low = 1 THEN Bottomline_Price ELSE NULL END) AS BotPriceGap
INTO ##Temp
FROM (
SELECT MobID,
Merchant,
Price,
Bottomline_Price,
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Bottomline_Price DESC) AS high,
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Bottomline_Price) AS low,
CASE Merchant
WHEN @usname THEN 1
ELSE 0
END AS My
FROM tbl_Merchant
) AS t
GROUP BY MobID
HAVING MAX(My) = 1

when using this code
output like this
low-price lowpricemerchant highprice highpricemerchant
74.99 TEPTRONICS 89.95 Amazon.com Marketplace
44.99 TEPTRONICS 9.96 Amazon.com
4.51 J&R 18.45 Limited Goods PG East
38.57 Amazon.com Marketplace 39.00 Buy.com
139.00 ezprogear 129.99 TEPTRONICS

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-03 : 08:19:26

highprice and lowprice are varies ... How to clear it

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-03 : 08:34:08

while using
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price DESC) AS high,
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price) AS low,

the low price & higprice problems eliminated ..But Bsaepricegap,botprice have some problems ..Help me

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-03 : 08:40:00

I think i need to have 2 loops for price and Bottomline_Price ....How to do

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources