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