Help to make SQL Code more efficient

[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

[justsomeguy]

The delay is probably because of the where conditions and the join. For every row that matches the other conditions it needs to execute dateadd and left on them, which is going to slow things down. That join looks like a full join, it's going to create every possible combination of rows from those two tables and then check those against the where conditions. If each of those tables has only 100 rows, then that join produces a table of 10,000 rows that it needs to check with dateadd and left. If you specify what kind of join you want to use there and also the join conditions then you can probably limit the size of that joined table. Also, make sure you have indexes defined on the LatestVersion and SourceSystemId columns, and probably ReceivedData and TradeDate also. It wouldn't matter to put an index on Timestamp or Timestamp1 because you're using the left function on it, an index wouldn't help there.You can also run those queries individually to see if any of the four is taking much longer than the others.