| Author |
 Topic  |
|
|
puppetbat
Starting Member
2 Posts |
 Posted - 06/07/2012 : 13:05:05
|
I have a database which serves as the back-end to an Intranet application and SSRS Reporting suite. It populates by querying a 3rd-party application database housed in another state at a sister location.
The 3rd-party application has been modified and the reporting pull no longer functions. This structure is likely to change again and again in the future. I cannot control the updates. My task is to make it work again, now and every time that it "breaks."
What would be the desired approach to avoid having to re-write the queries in 30 different places every time this thing changes? I was thinking of creating Views for the most commonly used items, but I keep running into a need to nest them, and I understand that nesting views is forbidden.
Example (not real table/column names):
The raw data is in AllPayments. I just need my location's payments, so I filter those with the MyPayments view.
But some payments are from patients, and some are from insurance companies. I was going to create a PatientPayments view and an InsurancePayments view, which sounds fine, but in order to avoid nesting and calling the MyPayments view, I have to repeat the MyPayments filters in both PatientPayments and InsurancePayments.
If I only had two subordinate queries, it wouldn't be a big deal, but I have more than 30. So when the MyPayments requirements changes, I have to rewrite 30 queries.
Is there a best practice to handle this kind of situation? I thought about using Table-Valued Functions, but I hear a lot of "No" about those, too.
Any ideas are welcome. |
|
|
robvolk
Most Valuable Yak
USA
15568 Posts |
Posted - 06/07/2012 : 13:18:10
|
quote:
I understand that nesting views is forbidden.
Who told you that? It's not forbidden, it's just usually a poor practice for performance reasons.
The problem that usually arises is having many joins on unindexed columns in large tables or views, which then get nested inside similar views. If you're building views against 1 or 2 tables that are properly indexed, you shouldn't have severe performance problems. And if you do, you may be able use indexed views to alleviate them. |
 |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3857 Posts |
Posted - 06/07/2012 : 13:23:03
|
Without knowing specifics it's hard to say. You might be able to pull data into a structure that you specify, and therefore avoid changing structure as rapidly as your source. Or, it might just be the price of changing requirements. Sometimes you just have to take the hit and make changes as part of the normal dev cycle. In my experience, when someone tries to get fancy/cute with a lot of views, sprocs and/or functions to avoid this work, things go bad. And when they go bad, there are so many layers of views, sprocs and functions that it becomes even more difficult to maintain.
That having been said, what you are doing may lend itself to some obfuscation/transformation via views. But, as alwasy, try to keep it simple (KISS). :)
|
|
|
|
puppetbat
Starting Member
2 Posts |
Posted - 06/07/2012 : 13:38:53
|
Performance speed isn't really a huge issue. There is a Stored Procedure that runs in the middle of the night that moves everything from one server to another. Right now, it takes about 8 minutes to populate the previous day's data into my reporting tables. There is no Real Time querying (unless it's me testing things out or doing 10-15 row lookups)
I had read that nesting views can cause problems. At most, I'd have three layers of nesting.
I am concerned about complexity and the opaque nature of a view calling another view.
My initial thought was to INSERT the MyPayments data into a table in the reporting database and then call the subordinate views off of that. I became concerned that transferring all of that data may be too much for the pipeline to handle (the servers talk to each other over a VPN) - is there a significant difference in server load when performing a SELECT statement over and INSERT INTO?
|
|
|
| |
Topic |
|
|
|
Avoiding Nested Views
