December 7, 2011 at 7:57 am
I have a job that is scheduled to run at 9 am, 1 pm, 3 pm and 7 pm. The data is used for our reports.
The job comprises of 7 stored procedures and each run as a step in the job.
The job takes around 45 minutes to execute. Because, it pulls the data on a transactional database, whilst the users are insert, updating,etc if brings the application down.
What options can I use to ensure that the data for reports is populated whilst the users are able to work?
December 7, 2011 at 8:15 am
You have not provided enough information to respond to your question.
These 7 sprocs, what do they do?
How do they do it?
Do they reload the report data from scratch?
Do they do updates?
Do they "bring the application down" because they lock 1 table or 10?
How is bringing the app down?
CEWII
December 7, 2011 at 11:32 pm
The 7 SPs populate data into temp tables that are used for the reports.
In each one, we drop the indexes, do SELECT from a combination of tables and INSERT into the temp tables. In some, we do UPDATE on those temp tables too. Then recreate the indexes.
The main tables used are quite big, ranging from 3million to 20million records.
Whilst the job is running, the users are also using the system, doing INSERTS, UPDATES,etc on those main tables.
I hope I have provided enough information.
December 8, 2011 at 8:56 am
It sounds like you are moving a lot of data around at those times. I've got to wonder if those sprocs are the best way. I tend to like to use SSIS to copy large chunks of data even from one DB to another. you might refactor one of the simpler ones as an SSIS package and see how fast it runs. Be sure in the destinations that the Fast Load option is selected. With a relatively simple query it is possible to copy an obscene amount of data in just seconds.
CEWII
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply