0

Procedure is created on SQL server

inserting the data from sql server to oracle databse

When individually run the query it executed in 10 sec

When run through procedure it takes sometime 1.5min and 5min

CREATE procedure sp_Trade_Book as
Begin
	SET NOCOUNT ON
	BEGIN TRY		--Begin of TRY BLOCK


		DELETE FROM [XXXX]..[XXX].[TRADE_REPORT] WHERE SUBSTRING(TRANSACT_TIME,1,8)
		IN (SELECT DISTINCT  SUBSTRING(TRANSACT_TIME,1,8) FROM TRADE_REPORT)
		
		INSERT INTO [XXXX]..[XXX].[TRADE_REPORT](Trade_Report_ID, Transact_Time, Trade_Status, 
		Exec_Type, Symbol, Buy_Clearing_Alpha, Buy_Broker_ID, Buy_Trader_ID, Buy_PAN_ID, Buy_Client_ID,
		Old_Buy_Client_ID, Executed_Qty, Executed_Value, Last_Trade_Net_Change,
		Sell_Clearing_Alpha, Sell_Broker_ID, Sell_Trader_ID, Sell_PAN_ID,
		Sell_Client_ID, Old_Sell_Client_ID, Trade_Report_Ref_ID, Trade_Report_Parent_ID) 
		SELECT Trade_Report_ID, Transact_Time, Trade_Status, Exec_Type,
 		Symbol, Buy_Clearing_Alpha, Buy_Broker_ID, Buy_Trader_ID, Buy_PAN_ID, Buy_Client_ID,
		Old_Buy_Client_ID, Executed_Qty, Executed_Value, Last_Trade_Net_Change,
		Sell_Clearing_Alpha, Sell_Broker_ID, Sell_Trader_ID, Sell_PAN_ID,
		Sell_Client_ID, Old_Sell_Client_ID, Trade_Report_Ref_ID, Trade_Report_Parent_ID  FROM TRADE_REPORT

		SELECT 1 AS STATUS 

	END TRY			--END of TRY BLOCK


	BEGIN CATCH		--Begin of CATCH BLOCK

		SELECT 0 AS STATUS		

	END CATCH		--End of CATCH BLOCK

END
2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by moone009
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.