L.Lamce

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