Loading rows from a view (no index)
Posted on 2013-06-19
we have a remote view from which we are pulling data into our local table.
The remote view is :REMOTE_CUSTOMER
and our local table is :LOCAL_CUSTOMER
This is what we are doing right now.
We first delete data from our local table
DELETE Top (50000) FROM LOCAL_CUSTOMER where Id = '209'
Then we do a select to pull the data from the view (the remote view does not have any indexes, can we assign a index to view? Also the remote view doesnt have a primary key)
SELECT FirstName,LastName,BusinessDescription,ProductDescription,ProductSourceMap,updateBy,updateTimestamp FROM remote.dbview.REMOTE_CUSTOMER
then we insert into our local table
INSERT INTO LOCAL_CUSTOMER (FirstName,LastName,BusinessDescription,ProductDescription,ProductSourceMap,updateBy,updateTimestamp,Id)
VALUES(:FirstName, :LastName, :BusinessDescription, :ProductDescription, :ProductSourceMap, :updateBy,:updateTimestamp,:Id)
There are over a million rows in the REMOTE_CUSTOMER view and this process takes around 30 minutes to get the data from the view and populate into our table.
Is there a way to improve the speed of this entire process?