Function taking too much time while executing

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

Transact-SQL (2005)

Function taking too much time while executing

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-09-25 : 08:49:09

Below is the code of one of my function's, it's give me the desired output but taking too much time while executing....

---------------------------------------------------------------------
CREATE FUNCTION [DBO].[Fn_Get_Consensus_Curve_41_Data]
(
@p_SessionId Int,
@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
-- =================================================================================================================

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
DBO.PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,DBO.GET_SESSION_MATCHDATE (@p_SessionId),101) And
Sector_Id = DBO.GET_SESSION_SECTORID(@p_SessionId) And
Location_Code = DBO.GET_SESSION_LOCATION (@p_SessionId) And
CustomerID = @p_CustId And
CustomerId Not In (Select CustomerId From DBO.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_SessionId, PRR.EntityID, @p_CustId, PRR.Date)

Declare Cur_Fetch_Curve_Entity_Data cursor for
Select Distinct EntityID
From
DBO.PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,DBO.GET_SESSION_MATCHDATE (@p_SessionId),101) And
Sector_Id = DBO.GET_SESSION_SECTORID(@p_SessionId) And
Location_Code = DBO.GET_SESSION_LOCATION (@p_SessionId) And
EntityId IN ( Select Distinct Entity_Id From DBO.Fn_Get_Allowed_Entity_List(@p_SessionId, @p_UserID )) And
IsNull(PRR.Record_Last_Action,'N') <> 'D' And
Version = DBO.GET_PRICESRR_MAX_VERSION(@p_SessionId, 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
DBO.PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,DBO.GET_SESSION_MATCHDATE (@p_SessionId),101) And
Sector_Id = DBO.GET_SESSION_SECTORID(@p_SessionId) And
Location_Code = DBO.GET_SESSION_LOCATION (@p_SessionId) And
CustomerId Not In (Select CustomerId From DBO.Fn_Get_PricesRR_Not_To_Include_Cust_Id('V')) And
IsNull(PRR.Record_Last_Action,'N') <> 'D'

Declare Cur_Fetch_Curve_Entity_Data cursor for
Select Distinct EntityID
From
DBO.PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,DBO.GET_SESSION_MATCHDATE (@p_SessionId),101) And
Sector_Id = DBO.GET_SESSION_SECTORID(@p_SessionId) And
Location_Code = DBO.GET_SESSION_LOCATION (@p_SessionId) And
IsNull(PRR.Record_Last_Action,'N') <> 'D'
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
DBO.GET_SESSION_LOCATION (@p_SessionId) Location_Code,
DBO.GET_SESSION_SECTORID(@p_SessionId) 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_SessionId, @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
DBO.Fn_Get_Tot_Curve_41_Date(@p_SessionId, @p_ENTITYID , @p_CUSTOMERID ,@p_UserID ) a,
DBO.referenceentity b,
(
Select customerid, mnemonic
From customersrr
Group By customerid,mnemonic
) c
Where
a.customerid = c.customerid and
a.EntityID=b.VCM_Entity_Id
) 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

CLOSE Cur_Fetch_Curve_Cust_Data
DEALLOCATE Cur_Fetch_Curve_Entity_Data
DEALLOCATE Cur_Fetch_Curve_Cust_Data

End

RETURN
END

-- =====================================================================

Can any one tell me where's the mistake???

Prashant

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 08:52:56

Why are you using cursor? what are you trying to do in the function? cant you try to implement a set based approach?

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-09-26 : 01:08:36

i am using cursor because i need to fetch customer wise entity's data.

can we used any other mechanism??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 01:19:38

quote:
Originally posted by hirani_prashant

i am using cursor because i need to fetch customer wise entity's data.

can we used any other mechanism??

can you illustrate what you're trying to do with some sample data and output. then we can see if this can be dealt on a set based approach.

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-09-26 : 04:52:15

okay.

i do have one customer [i.e 102003] and the customer is involved with 8 entities (i.e. 1111, 1112, 1113, 1114, 1115, 1116, 1117, 1118)

Now the thing is that i need to show all the entities data for that particular customer..

so what i have done, in first cursor i am taking customer's list [there is n numbers of customers] and inside that curosor i am using second cursor which gives me entity list [n number of entity for that particular customer]...

i hope now i am clear....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 04:56:46

quote:
Originally posted by hirani_prashant

okay.

i do have one customer [i.e 102003] and the customer is involved with 8 entities (i.e. 1111, 1112, 1113, 1114, 1115, 1116, 1117, 1118)

Now the thing is that i need to show all the entities data for that particular customer..

so what i have done, in first cursor i am taking customer's list [there is n numbers of customers] and inside that curosor i am using second cursor which gives me entity list [n number of entity for that particular customer]...

i hope now i am clear....

i dont think you need cursor here. what you want is simple join
something like

SELECT *
FROM Customer c
INNER JOIN CustomerEntity ce
ON ce.Customer_ID=c.Customer_ID

b/w is your attemp to bring related data of customer in a single row along with customer detail in a row. Illustrating it with some sample data will help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources