SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Function taking too much time while executing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 09/25/2008 :  08:49:09  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 09/25/2008 :  08:52:56  Show Profile  Reply with Quote
Why are you using cursor? what are you trying to do in the function? cant you try to implement a set based approach?
Go to Top of Page

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 09/26/2008 :  01:08:36  Show Profile  Reply with Quote
i am using cursor because i need to fetch customer wise entity's data.

can we used any other mechanism??
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/26/2008 :  01:19:38  Show Profile  Reply with Quote
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.
Go to Top of Page

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 09/26/2008 :  04:52:15  Show Profile  Reply with Quote
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....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/26/2008 :  04:56:46  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000