Want to reduce execution time...

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Want to reduce execution time...

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-04-02 : 06:15:07

Hello all,

Mine below function takes much time at every execution. It takes 0.18 sec to retrive 984 rows.

Can any one help me, how to reduce execution time?

"Create function [dbo].[Fn_Get_Consensus_Curve_41_Data]
(@p_Location_Code nvarchar(10), @p_Sector_Id int, @p_Match_Date DateTime ,@p_UserID int , @p_CustId int)

RETURNS @Temp_Curve_Submission_Data table
(
Location_Code nvarchar(10),
Sector_Id int ,
MatchDate datetime ,
EntityId int ,
CustomerId int ,
MaturityDate datetime ,
Cust_Price float ,
Bid_Price float ,
Offer_Price float ,
Consensus_Mid_Price float ,
Ticker nvarchar(20) ,
Cust_Mnemonic nvarchar(50) ,
Currency_Id int
)
as
BEGIN
/*
GO

IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'Fn_Get_Consensus_Curve_41_Data')
AND ((type = 'P') OR (type = 'IF') OR (type = 'TF') OR (type = 'FN'))))
DROP FUNCTION [dbo].Fn_Get_Consensus_Curve_41_Data

GO

*/
declare @p_ENTITYID INT
declare @p_CUSTOMERID INT

Declare @p_Login_Type int
Declare @p_Result_Status int
set @p_Login_Type = (SELECT DBO.GET_USER_LOGIN_TYPE_ID(@p_UserID))

If @p_Login_Type=1 and not (@p_CustId is null or @p_CustId='')
Set @p_Result_Status = 1
Else if @p_Login_Type > 1
Set @p_Result_Status = 2
Else
Set @p_Result_Status = 0

If @p_Result_Status > 0 -- if user is valid and given enough parameters than
Begin
If @p_Result_Status = 1 -- if User is trader and gives customer id
Begin
Declare Cur_Fetch_Curve_Cust_Data cursor for
Select Distinct Customerid
From PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And
Sector_Id = @p_Sector_Id And
Location_Code = @p_Location_Code And
CustomerID = @p_CustId And
--CustomerID <> 0
--CustomerID not in (0, -1, -2, -3, -100, -200)
CustomerId Not In (Select CustomerId From Fn_Get_PricesRR_Not_To_Include_Cust_Id('V'))
and isnull(PRR.Record_Last_Action,'N') <> 'D'
and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)

Declare Cur_Fetch_Curve_Entity_Data cursor for
Select Distinct EntityID
From PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And
Sector_Id = @p_Sector_Id And
Location_Code = @p_Location_Code
AND EntityId IN ( Select Distinct Entity_Id from Fn_Get_Allowed_Entity_List(@p_Location_Code , @p_Sector_Id , @p_Match_Date ,@p_UserID ))
and isnull(PRR.Record_Last_Action,'N') <> 'D'
and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)

End
Else If @p_Result_Status = 2 -- if User is higher than trader.. means broker or higher
Begin
Declare Cur_Fetch_Curve_Cust_Data cursor for
Select Distinct Customerid
From PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And
Sector_Id = @p_Sector_Id And
Location_Code = @p_Location_Code And
--CustomerID <> 0
--CustomerID not in (0, -1, -2, -3, -100, -200)
CustomerId Not In (Select CustomerId From Fn_Get_PricesRR_Not_To_Include_Cust_Id('V'))
and isnull(PRR.Record_Last_Action,'N') <> 'D'
--and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)

Declare Cur_Fetch_Curve_Entity_Data cursor for
Select Distinct EntityID
From PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And
Sector_Id = @p_Sector_Id And
Location_Code = @p_Location_Code
and isnull(PRR.Record_Last_Action,'N') <> 'D'
--and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)

End
delete from @Temp_Curve_Submission_Data

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

Open Cur_Fetch_Curve_Cust_Data
fetch next from Cur_Fetch_Curve_Cust_Data
into @p_CUSTOMERID
WHILE @@FETCH_STATUS = 0
BEGIN

IF @@FETCH_STATUS <> 0 break
BEGIN
-----------------------
-----------------------
Open Cur_Fetch_Curve_Entity_Data
fetch next from Cur_Fetch_Curve_Entity_Data
into @p_ENTITYID
WHILE @@FETCH_STATUS = 0
BEGIN

IF @@FETCH_STATUS <> 0 break
-----------------------
Insert Into @Temp_Curve_Submission_Data
(
Location_Code ,
Sector_Id ,
MatchDate ,
EntityId ,
CustomerId ,
MaturityDate ,
Cust_Price ,
Bid_Price,
Offer_Price,
Consensus_Mid_Price ,
Ticker ,
Cust_Mnemonic ,
Currency_Id
)

select
@p_Location_Code Location_Code,
@p_Sector_Id Sector_Id,
X.MatchDate Match_Date,
X.EntityId Entity_Id ,
X.CustomerId Customer_Id,
X.MaturityDate Maturity_Date,
X.Price Cust_Price,
X.BidValue Bid_Price,
X.OfferValue Offer_Price,
DBO.GET_Consensus_MID ('V',@p_Location_Code , @p_Sector_Id , @p_Match_Date, @p_ENTITYID ,x.MaturityDate) Consensus_Mid_Price,
--DBO.GET_Consensus_MID ('B1',@p_Location_Code , @p_Sector_Id , @p_Match_Date, @p_ENTITYID ,x.MaturityDate) Consensus_Mid_Price,
X.Ticker Ticker ,
X.Mnemonic Cust_Mnemonic,
X.Currency_Id
from
(
SELECT
row_number() over (order by maturitydate) Line_No,
a.* ,
b.ticker,
c.mnemonic
from Fn_Get_Tot_Curve_41_Date(@p_Location_Code, @p_Sector_Id, @p_Match_Date, @p_ENTITYID , @p_CUSTOMERID ,@p_UserID ) a,
referenceentity b,
(
select customerid, mnemonic
from customersrr
group by customerid,mnemonic
) c
where
a.customerid = c.customerid and
a.EntityID=b.CMAID
--order by maturitydate
) X

-----------------------
Fetch Next From Cur_Fetch_Curve_Entity_Data
Into @p_ENTITYID

END
CLOSE Cur_Fetch_Curve_Entity_Data

END
-----------------------
-----------------------

Fetch Next From Cur_Fetch_Curve_Cust_Data
Into @p_CUSTOMERID

END
deallocate Cur_Fetch_Curve_Entity_Data
CLOSE Cur_Fetch_Curve_Cust_Data
deallocate Cur_Fetch_Curve_Cust_Data

End

return
end
"

Prashant Hirani

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-02 : 06:19:37

What's those two cursors doing there? You should convert this to set-based logic to improve the performance. Also, you need to check called functions to verify whether there is any chance of improvement there.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources