Data population slows down application performance

Question

Data population slows down application performance

arihantjain121189

SSC-Addicted

Points: 407
More actions
November 1, 2018 at 12:47 pm

#372736

We need to load data from source table to summary tables on daily basis. But we don't want to slow down our application performance while data population as the data is fetched from summary table using stored procedure to show on the application.
How can we do it?
My suggestion is that we should run oir population process in separate database and once the data is loaded into summary tables we will move data from there to here. Also i have no idea if the databases on the same machine still slow down the performance or the databases should be on different machine.
Please suggest me better way than what i have suggested.
Thanks

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

arihantjain121189 - Thursday, November 1, 2018 12:47 PM
We need to load data from source table to summary tables on daily basis. But we don't want to slow down our application performance while data population as the data is fetched from summary table using stored procedure to show on the application.
How can we do it?
My suggestion is that we should run oir population process in separate database and once the data is loaded into summary tables we will move data from there to here. Also i have no idea if the databases on the same machine still slow down the performance or the databases should be on different machine.
Please suggest me better way than what i have suggested.
Thanks

The only way to ensure that loading data into a database does not slow down the performance of another database is to put the target database on a different server, which does not share any resources with the application server in terms of CPU, disk, RAM or network bandwidth.

andycadley SSCertifiable Points: 5296 More actions · Answer 2

If you're just trying to refresh the summary data and don't want the calculation times to impact queries, consider building the new summaries in a separate table and then using one of the metadata operations to swap the summaries out. I prefer partition switching, but schema switching or synonyms can be used to accomplish the same basic thing.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

andycadley - Thursday, November 1, 2018 1:25 PM
If you're just trying to refresh the summary data and don't want the calculation times to impact queries, consider building the new summaries in a separate table and then using one of the metadata operations to swap the summaries out. I prefer partition switching, but schema switching or synonyms can be used to accomplish the same basic thing.

Totally agree. If you have the room, synonym swaps work really well. And, if something goes wrong with the new load, you can keep the old one online and retry on the new one and most folks simply won't notice.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)