March 8, 2010 at 6:31 am
Hi there,
I'm working on a very complex report and want the running time to be as minimal as possible. the report displays financial data and will be used by multiple users every day.
We have a database called ReportDefinitions which has sevearl objects in it (tables etc) and this database is used for different reports. For example, a lot of the tables are budget tables which are used by reports.
I wanted to ask for advice. I was thinking about setting up a dedicated database for the financial report. It will contain about 3 tables that are populated using SPs every night and via a job that runs every 30 minutes during the day. However, I could use the ReportDefintions database. Would it be better to set a new database for this report? Maybe this will ensure it's efficient etc? Or should I Just use what's already there?
Hope someone can help
Cheers
March 8, 2010 at 6:52 am
pablavo (3/8/2010)
Hi there,I'm working on a very complex report and want the running time to be as minimal as possible. the report displays financial data and will be used by multiple users every day.
We have a database called ReportDefinitions which has sevearl objects in it (tables etc) and this database is used for different reports. For example, a lot of the tables are budget tables which are used by reports.
I wanted to ask for advice. I was thinking about setting up a dedicated database for the financial report. It will contain about 3 tables that are populated using SPs every night and via a job that runs every 30 minutes during the day. However, I could use the ReportDefintions database. Would it be better to set a new database for this report? Maybe this will ensure it's efficient etc? Or should I Just use what's already there?
Hope someone can help
Cheers
Hi pablavo, it is always better to have the reports' source in a different database. There are many reasons for this.
a) Makes sure that your transactions will not be kept waiting.
b) Lets you set different thresholds for memory etc. (if you use a different server for reporting)
c) You can restart the report database server without having a down time. (if you use a different server for reporting)
However if you decide to use the same database, please add query hints in your queries.
select reportdatafield1, reportdatafield2 from dbo.reporttable a with (nolock, readuncommitted)
PS: Please remember that using nolock/readuncommitted can cause data consistency problem.
https://sqlroadie.com/
March 9, 2010 at 4:21 am
Thanks Arjun, this advice will help with implementation.
Cheers
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply