Hi There,
I have a product table with about 1.5mil records and a stock table with a similar amount of data. I have set up a view that (inner) joins the stock onto the product data and performs a few more calcuations on the data. There are then indexes (normal and full text with change tracking) set up on the view for searching on the front end. (none of the indexes on the view directly reference the data from the stock table, however a product has to have a stock record in order to be included)
This is working well from a front end perspective.
My situation is that I need to completely refresh the stock data twice a day (which comes in CSV format). I am looking for a seamless and effecient route to do this without causing any downtime on the front end, as well as being fast / effeicent..
I have a couple of ideas:
1. Set up a SSIS package to: Delete existing data and Import new data direct into the stock table.
This works, and runs in under 5 mins, however I am concerned about the amount of knock on this will have in terms of recalculating the view / indexes etc. Presumably there will also be "down time" while everthing completes.
2. Set up a tempoary table for the stock data and import as above, then update the main stock table from the temp table using and update statement. This will update all records that appear in both tables. As there will be addtions and omissions, presumably I could then run insert and delete statements for the differences between the tables.
I have not tried this, so am unsure of the performance, however I think that there will not be any knock on to the indexes where it is not nessecary, and therefore avoid any recalcuation here, and also avoid downtime while tables are emptied and re-created?
I would appreciate your thoughts on this, and any other ideas on how this could be achieved.
Thanks
