Jump to content

L.Lamce

Members
  • Posts

    1
  • Joined

  • Last visited

L.Lamce's Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. Hello, i am trying to retrieve data from five different tables and union the tables into one. I was wondering if there is anything I can do in my coding to make it more efficient as it is taking considerable time to run. The code is: SELECT 'Order' As Order_Trade ,[sourceSystemId] ,[ReceivedData] As Order_Trade_Date ,[ReceivedTime] As Order_Trade_Time ,[LastExecutor] As Trader ,[ExecutingBookId] As Book ,[OrderId] As Order_Trade_ID ,[buySell] ,[CounterpartyCode] ,[ExchangeId] ,[Version] As VersionNumber ,[instrumentCode] ,[TotalQuantity] As Order_Trade_Quantity ,[OrderNotes] As Order_Trade_Notes ,[OrderDesk] As Desk FROM [CoreRDSPrd].[core].[tblRdsTsHostedOrderProgress] WHERE [LatestVersion] = 'Y' AND [sourceSystemId] = '62' OR [sourceSystemId] ='65' AND [ReceivedData] > DATEADD(d, -2, current_timestamp) ---------------------------------- UNION SELECT 'Order' As Order_Trade ,[sourceSystemId] ,LEFT([Timestamp], 8) As Order_Trade_Date ,SUBSTRING([Timestamp], 9,9) As Order_Trade_Time ,[ClEnteredBy] As Trader ,[ClBookId] As Book ,[ClOrderId] As Order_Trade_ID ,[ClBuySell] As BuySell ,[ClCounterpartyCode] As CounterpartyCode ,[ExchangeId] ,[ClVersion] As VersionNumber ,[instrumentCode] ,[ClQuantity] As Order_Trade_Quantity ,[ClOrderNotes] As Order_Trade_Notes ,[OrderDesk] As Desk FROM [CoreRDSPrd].[core].[tblRdsTsOrder] WHERE [LatestVersion] = 'Y' AND[sourceSystemId] = '62' OR [sourceSystemId] ='65' AND LEFT([Timestamp], 8) > DATEADD(d, -2, current_timestamp) ---------------------------------- UNION SELECT 'Trade' As Order_Trade ,t.[sourceSystemId] ,ts.[ClTradeDate] As Order_Trade_Date ,ts.[ClTradeTime] As Order_Trade_Time --,LEFT([Timestamp1],8) As Order_Trade_Date --,SUBSTRING([Timestamp1], 9,9) As Order_Trade_Time ,t.[updateUser] As Trader ,t.[ClBookId] As Book ,t.[ClTradeId] As Order_Trade_ID ,t.[ClBuySell] As BuySell ,t.[ClCounterpartyCode] As CounterpartyCode ,t.[ExchangeId] ,t.[ClVersion] As VersionNumber ,t.[instrumentCode] ,t.[ClQuantity] As Order_Trade_Quantity ,t.[ClBusinessTransaction] As Order_Trade_Notes ,'111111' As Desk FROM [CoreRDSPrd].[core].[tblRdsTsTrade] t, [CoreRDSPrd].[core].[tblRdsTsTradeSet] ts WHERE ts.[LatestVersion] = 'Y' AND t.[sourceSystemId] = '62' OR t.[sourceSystemId] ='65' AND ts.ClTradeSetId=t.ClTradeSetId AND LEFT(t.[Timestamp1], 8) > DATEADD(d, -2, current_timestamp) ---------------------------------- UNION SELECT 'Trade' As Order_Trade ,[sourceSystemId] ,[TradeDate] As Order_Trade_Date ,[Tradetime] As Order_Trade_Time ,[Trader] ,[bookId] As Book ,[TradeId] As Order_Trade_ID ,[buySell] ,[Counterparty] As CounterpartyCode ,[ExchangeId] ,[VersionNumber] ,[RICCode] As InstrumentCode ,[Quantity] As Order_Trade_Quantity ,[businessTransaction] As Order_Trade_Notes ,[CustomSource] As Desk FROM [CoreRDSPrd].[core].[tblRdsTsEtpMarketTrade] WHERE [LatestVersion] = 'Y' AND [TradeDate] > DATEADD(d, -2, current_timestamp) AND [sourceSystemId] = '62' OR [sourceSystemId] ='65' is there anything that can be done to make this query quicker, or is the lag as a result of the many rows which i expect it to return? Thanks, Lorijent Lamce
×
×
  • Create New...