Insert/Update to sql server DB in a scheduler
I am importing external data(oracle) to local sql server DB thru linked server. This is actual replica of the external data(not normalized). I need to run this import for every 2 minutes. Its working well.
Now, I need to split this data into child tables to fulfil the normalization. For that, I have created a C# class which gets all records(newly added/modified) and passing these values to stored procedure as parameters.
The SP will check if that record exists, it will update or it will insert into my local tables.
I made DLL and I need to run this DLL for every 2 minutes(this is my client requirement). If I have 1500 records, this import consumes 10 minutes(to establish connection, retrieve, and calling SP).
But this is more time. Its not acceptable.
How can I reduce the time? Is it correct way what I am doing or any other way is possible?
Pls suggest me an idea.
Thanks in advance