Reference and production base
I need to run calculations on a very large database. The application slows down significantly while performing calculations. Possible work would be to copy this to a second database and perform the calculations offline. When it does, switch it over with the live database. Can this process be automated?
is this what Google is doing with its search index?
Of course, it can be automated too. But the data cannot be changed if you are going to change it back.
I would only pass the data to be transferred and aggregate what data can be aggregated during the transfer and index the destination tables to optimize the calculation. However, both of these options are possible without fetching data from the database and simply creating other tables in the database.
If you are performing an operation on the same computer, you may still experience the same sluggishness as the server will perform the same operations ...
If the "slowness" is actually caused by a behvaior lock / lock and the data will not change, you can use an option WITH(NOLOCK)
in your tables to avoid blocking other operations during long operations.
a source to share
I would say that the best idea would be to extract the computation into your own module, and then run that separately in another process or on another computer using the same database. It's hard to say for sure if it's the right thing to do without more information on your specific setup.
a source to share