SQL Server 2008 R2
Migrating data from a production database into a reporting database every morning. Because of the way the data is collected and updated in the production database, I must look back as much as 30 days to pull values for a SourceID and ProductionDate into the reporting database. This 30 day recordset returns about 42000 records, almost instantaneously (this is the "source").
The "target" table contains several decades worth of data (over 300M records) indexed by the same SourceID and ProductionDate.
What I have been doing is merging the "source" data into the reporting database table, updating the records which already exist with data from the "source" and appending records which don't already exist. I assumed this would be quicker than performing a delete operation and an insert, but lately the time it takes to run this simple merge operation has grown from about 40 minutes to over an 1:45:00 (give or take). And I cannot figure out what caused this huge increase in time.
1. Would it be quicker to perform a delete and than an append, than the merge
2. What steps should I take to determine the cause of the increase in time?
3. Instead of using the entire table in the reporting database as the target, can I somehow use a subset of the table as the target? Something like:
MERGE SELECT * from tbl_Reports WHERE ProductionDate >= @FromDate as Target
MERGE vw_Reports_Table_Last35days as Target
which would use an updateable view as the target?