L.Lamce Posted February 11, 2015 Share Posted February 11, 2015 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted February 11, 2015 Share Posted February 11, 2015 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now