May 16, 2011 at 7:06 am
I have a database here that is meant strictly for reporting. What's included in the refresh and ultimately what ends up in the database is controlled by IT. As far as other departments go, one department may want to view the data in the database one way, and another department may want to view it another way (I know that's fairly common). OK, to accommodate that, I can keep the raw data in the tables and create views to meet the differing needs of the departments. However, I'm looking for opinions from forum members on where those views should reside. Here's what I mean:
It's a reporting database with raw data -- something tells me I should keep it that way and not begin creating views each time a department requests a new view of the data (not to mention it keeps the database cleaner and I can keep permissions to read-only).
Some departments have their own database or server (w/ SQL Server), so I wonder if the practice should be for them to create views on their servers and just go through a linked server to get the data from the reporting database. If this is the way to go, should there be a database created for each department that doesn't have its own database?
The other option would be to create 'views' in a reporting tool itself, and so the reporting database would remain untouched, and all manipulation would take place in the metadata layer of whichever reporting tool that is chosen. One problem with this method is that we don't have the reporting tool yet, so I don't know what to expect of the 'view'-making capabilities of the software. Also, views on SQL server would probably outperform those created in the reporting tool.
Anyway, do you have any other thoughts on this type of situation? Is it not a good idea to seal off the reporting database to keep it clean and simple?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
May 16, 2011 at 8:24 am
First off, if it is a reporting only system that gets a refresh at night or something, make it read only. That will radically improve your performance for the queries.
As to your specific questions... I would lean heavily towards setting up views on that database and having them run from there. Although, I'd be careful about how the views are used because you don't want to see the development teams starting to nest the views and join one view to another. That can lead to serious performance bottlenecks. I prefer this because it means you can monitor access much better than if they're using linked servers. Plus, if they start using linked servers they're again inclined to start joining against the linked data, which, depending again on how they're querying, can cause a lot more reads and network traffic than if they were simply pulling the data they actually needed from your original reporting system.
With more details on the needs of this system I might qualify my answers more one way or another, but this is the general approach I'd take.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2011 at 8:30 am
What's the advantage of setting the db or files to read_only?.. and which ones do you set ro?
May 16, 2011 at 8:33 am
No locks are needed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 8:38 am
Gail said it. Read only not only means it doesn't take out locks, but it stops trying to. Changes the dynamic on queries considerably. Huge advantage for read only systems. But... it really has to be a read only system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2011 at 8:39 am
Ninja's_RGR'us (5/16/2011)
What's the advantage of setting the db or files to read_only?.. and which ones do you set ro?
Ya, but if the db only has selects running on it, how much can that locking slow the server down?
May 16, 2011 at 8:42 am
Depends. It's not just the blocking, it's that SQL doesn't bother taking any locks at all. Gone is the time required to ask for locks, overhead of lock memory, checking for lock escalations, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 8:42 am
Ninja's_RGR'us (5/16/2011)
Ninja's_RGR'us (5/16/2011)
What's the advantage of setting the db or files to read_only?.. and which ones do you set ro?Ya, but if the db only has selects running on it, how much can that locking slow the server down?
You're unlikely to get contention, but you actually do get a performance boost as less memory and processor power is used in managing locks. It's a nice trick if you're in position to take advantage of it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2011 at 8:48 am
GilaMonster (5/16/2011)
Depends. It's not just the blocking, it's that SQL doesn't bother taking any locks at all. Gone is the time required to ask for locks, overhead of lock memory, checking for lock escalations, etc.
Cool, I guess I never had so many users that this was an issue...
Will keep that in mind when I had ro server db.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply