Help in Query writing - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help in Query writing

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-29 : 05:04:06

Hi,

Mine Below Query is taking too much execution time. I am using this query in one of my sp.
Is there any other way to write this query?

Declare @p_Location_Code nvarchar(10)
Declare @p_ShowNearFarRate int
DECLARE @p_Data_Show_Flag int

Set @p_ShowNearFarRate = 1
Set @p_Data_Show_Flag = 1

select
X.Main_Party_Role ,
X.SET_ID,
X.TradeVisionId ,
X.EntityId ,
DBO.GET_ENTITY_TICKER (X.EntityId) Ticker,
X.Done ,
X.MatchDate ,
X.Main_Party_Id,
X.BUYER_MNEMONIC,
X.SELLER_MNEMONIC,
X.Other_Party_Id,
DBO.GET_DDMONYYYY_DATE_FORMAT(X.Main_Near_Dt) Main_Near_Dt,
DBO.GET_DDMONYYYY_DATE_FORMAT(X.Main_Far_Dt) Main_Far_Dt,
X.Amounts,
'Amount' = Case When ((ISNULL(X.Amount,0) >20) ) then Case When @p_Location_Code = 'NY44' then '20+' Else convert(nvarchar,x.Amount) End Else convert(nvarchar,x.Amount) End,
X.Price,
X.Duration,
X.Other_Party_Bro,
X.Main_Party_Bro,
X.Main_Party_State,
X.Other_Party_State,
'Main_Party_Near_Parse' = Case When (X.Main_Party_State=184 and X.Other_Party_State=184) THEN X.Main_Party_Near_Parse Else Case When @p_ShowNearFarRate = 1 then X.Main_Party_Near_Parse End End,
'Main_Party_Far_Parse' = Case When (X.Main_Party_State=184 and X.Other_Party_State=184) THEN X.Main_Party_Far_Parse Else Case When @p_ShowNearFarRate = 1 then X.Main_Party_Far_Parse End End,
X.Main_Party_Spread BPS,
'Main_Party_Amt' = Case When @p_Location_Code <> 'NY44' then X.Main_Party_Amt Else Case When (ISNULL(X.DONE,0) = 1 or X.Main_Party_State = 184 or X.Main_Party_State = 106) then X.Main_Party_Amt Else Case When ISNULL(X.Main_Party_Amt,0) <= 20 then X.Main_Party_Amt Else 20 End End End,
X.Other_Party_Near_Parse,
X.Other_Party_Far_Parse,
X.Other_Party_Spread,
X.Other_Party_Amt,
X.LOCATION_CODE ,
X.Color_Value Org_Color_Value,
'Color_Value' = Case When @p_Location_Code = 'NY44' then
Case When X.Color_Value=500 then 500 + X.CallMe_Btn_Value
Else Case When X.Color_Value>= 284 and Isnull(x.done,0) <> 1 then 284 + X.CallMe_Btn_Value
Else Case When X.Color_Value=201 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=200 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=184 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=106 then 16 + X.CallMe_Btn_Value
Else Case When X.Color_Value=101 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=100 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=16 then 16 + X.CallMe_Btn_Value
Else 0 + X.CallMe_Btn_Value
End
End
End
End
End
End
End
End
End
Else
Case When X.Color_Value>=300 then 300 + X.CallMe_Btn_Value
Else Case When X.Color_Value=284 then 284 + X.CallMe_Btn_Value
Else Case When X.Color_Value=201 then 201 + X.CallMe_Btn_Value
Else Case When X.Color_Value=200 then 200 + X.CallMe_Btn_Value
Else Case When X.Color_Value=184 then 184 + X.CallMe_Btn_Value
Else Case When X.Color_Value=106 then 106 + X.CallMe_Btn_Value
Else Case When X.Color_Value=101 then 101 + X.CallMe_Btn_Value
Else Case When X.Color_Value=100 then 100 + X.CallMe_Btn_Value
Else Case When X.Color_Value=16 then 16 + X.CallMe_Btn_Value
Else 0 + X.CallMe_Btn_Value
End
End
End
End
End
End
End
End
End
End,
X.Main_Party_Strike_State,
X.Other_Party_Strike_State,
'BACK_COLOR' = (DBO.Fn_Get_TradeVisionRR_Back_Color_Value (@p_Location_Code, X.Color_Value)) ,
'FORE_COLOR' = (DBO.Fn_Get_TradeVisionRR_Fore_Color_Value (@p_Location_Code, X.Color_Value))
FROM
(
SELECT
Y.TradeVisionFileId SET_ID,
'BUYER' Main_Party_Role,
Y.TradeVisionId ,
Y.EntityId ,
Y.Done ,
Y.MatchDate ,
Y.BuyerId Main_Party_Id,
DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERId) BUYER_MNEMONIC,
'SELLER_MNEMONIC' = Case When Y.Done = 1 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERID)
Else Case When ( @p_Location_Code <> 'NY44' AND Y.BuyerState = 184 AND Y.BuyerState = 184 AND Y.IsBNearPraceTouched = 1 AND Y.IsSNearPraceTouched = 1 AND Y.IsBFarPraceTouched = 1 AND Y.IsSFarPraceTouched = 1 AND Y.IsBSpreadTouched = 1 AND Y.IsSSpreadTouched = 1 AND Y.IsBAmtTouched = 1 AND Y.IsSAmtTouched = 1 AND Y.BuyerNearPrace = Y.SellerNearPrace AND Y.BuyerFarPrace = Y.SellerFarPrace AND Y.BuyerSpread = Y.SellerSpread AND Y.BuyerAmt = Y.SellerAmT) THEN DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERID)
Else Case When isnull(@p_Data_Show_Flag,1)=3 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERId)
Else '*'
End
End
End,
Y.SellerId Other_Party_Id,
Y.NearDate Main_Near_Dt,
Y.FarDate Main_Far_Dt,
Y.Amounts Amounts,
Y.Amount Amount,
Y.Price Price,
Y.Duration Duration,
Y.SellerBro Other_Party_Bro,
Y.BuyerBro Main_Party_Bro,
Y.BuyerState Main_Party_State,
Y.SellerState Other_Party_State,
Y.BuyerNearPrace Main_Party_Near_Parse,
Y.BuyerFarPrace Main_Party_Far_Parse,
Y.BuyerSpread Main_Party_Spread,
Y.BuyerAmt Main_Party_Amt,
Y.SellerNearPrace Other_Party_Near_Parse,
Y.SellerFarPrace Other_Party_Far_Parse,
Y.SellerSpread Other_Party_Spread,
Y.SellerAmt Other_Party_Amt,
Y.LOCATION_CODE ,
Y.Buyer_Strike_State Main_Party_Strike_State,
Y.Seller_Strike_State Other_Party_Strike_State,
(DBO.GET_TRADEVISIONRR_TRADEVISIONID_CUST_STATUS('NY44', 18, Y.TradeVisionId,Y.TRADEVISION_VERSION ,'BUYER' )) Color_Value ,
'CallMe_Btn_Value' = Case When Y.Buyer_Strike_State = 206 then 1000 Else 0 End
from DBO.TradeVisionRR Y
WHERE
Y.LOCATION_CODE = @p_Location_Code
AND Y.TRADEVISION_VERSION = (DBO.GET_TRADEVISIONRR_SET_MAX_VERSION('NY44', '02/05/2008', 169 ,Y.TradeVisionId ))
AND Y.TRADEVISIONFILEID = 169
------------------------------------------------------------
UNION ALL
------------------------------------------------------------
select
Y.TradeVisionFileId SET_ID,
'SELLER' Main_Party_Role,
Y.TradeVisionId ,
Y.EntityId ,
Y.Done ,
Y.MatchDate ,
Y.SellerId Main_Party_Id,
'BUYER_MNEMONIC' = Case When Y.Done = 1 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERID)
Else Case When (@p_Location_Code <> 'NY44' AND Y.BuyerState = 184 AND Y.BuyerState = 184 AND Y.IsBNearPraceTouched = 1 AND Y.IsSNearPraceTouched = 1 AND Y.IsBFarPraceTouched = 1 AND Y.IsSFarPraceTouched = 1 AND Y.IsBSpreadTouched = 1 AND Y.IsSSpreadTouched = 1 AND Y.IsBAmtTouched = 1 AND Y.IsSAmtTouched = 1 AND Y.BuyerNearPrace = Y.SellerNearPrace AND Y.BuyerFarPrace = Y.SellerFarPrace AND Y.BuyerSpread = Y.SellerSpread AND Y.BuyerAmt = Y.SellerAmT) THEN DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERID)
Else Case When isnull(@p_Data_Show_Flag,1)=3 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERID)
Else '*'
End
End
End,
DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERID) SELLER_MNEMONIC,
Y.BuyerId Other_Party_Id,
Y.NearDate Main_Near_Dt,
Y.FarDate Main_Far_Dt,
Y.Amounts Amounts,
Y.Amount Amount,
Y.Price Price,
Y.Duration Duration,
Y.BuyerBro Other_Party_Bro,
Y.SellerBro Main_Party_Bro,
Y.SellerState Main_Party_State,
Y.BuyerState Other_Party_State,
Y.SellerNearPrace Main_Party_Near_Parse,
Y.SellerFarPrace Main_Party_Far_Parse,
Y.SellerSpread Main_Party_Spread,
Y.SellerAmt Main_Party_Amt,
Y.BuyerNearPrace Other_Party_Near_Parse,
Y.BuyerFarPrace Other_Party_Far_Parse,
Y.BuyerSpread Other_Party_Spread,
Y.BuyerAmt Other_Party_Amt,
Y.LOCATION_CODE ,
Y.Seller_Strike_State Main_Party_Strike_State,
Y.Buyer_Strike_State Other_Party_Strike_State,
(DBO.GET_TRADEVISIONRR_TRADEVISIONID_CUST_STATUS('NY44', 169, Y.TradeVisionId,Y.TRADEVISION_VERSION ,'SELLER' )) Color_Value ,
'CallMe_Btn_Value' = Case When Y.Seller_Strike_State = 206 then 1000 Else 0 End
from DBO.TradeVisionRR Y
WHERE
Y.LOCATION_CODE = @p_Location_Code
AND Y.TRADEVISION_VERSION = (DBO.GET_TRADEVISIONRR_SET_MAX_VERSION('NY44', '02/05/2008', 169 ,Y.TradeVisionId ))
AND Y.TRADEVISIONFILEID = 169
) X
where
X.EntityId IN ( Select Distinct Entity_Id from Fn_Get_Allowed_Entity_List('NY44' , 169 , '02/05/2008' ,200000 ))
and X.Main_Party_Id = 101901
Order By X.TradeVisionId

-- Regards
Prashant Hirani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 06:09:06

I can see some redundant code in the derived table. What the difference between two select batches seperated by UNION ALL? it seems like the conditions are all same

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-29 : 06:26:21

Thanks for reply.

I am splitting that information into two parts according to their Main_Party_Role('Buyer' and 'Seller').

I need to show that information that's why i am using union all condition.

This Query is giving me desired output. The only thing for me now is to reduce the execution time.

--Thanks
Prashant

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-30 : 01:40:57

Hi,

Can any one please help me??

Thanks
Prashant Hirani

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-30 : 03:07:07

would creating index gives me faster execution??

If so then please tell me which type of index should i have to create?

THanks
Prashant

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-06-05 : 06:01:58

Hello,

No one can guide me? At least give me some suggestion or tell me where i am wrong?

Thanks
Prashant

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-06-05 : 06:12:53

Hello,

No one can guide me? At least give me some suggestion or tell me where i am wrong?

Thanks
Prashant

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 06:15:41

Its very difficult by looking at your code which is not properly indented. I think you could use execution plan while runing sp and analyse it to find out costly queries and try optimizing it may be by introduction some indexes or rewriting of logic.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources