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

exec sp_recompile sp_Trade_Book

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.