Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

If I understand Views VS Functions correctly...I should use views to enapsulated commonly used where clauses? Expand / Collapse
Author
Message
Posted Friday, May 2, 2014 1:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:01 AM
Points: 47, Visits: 175
Some background to my question. I am working with healthcare data and my datasource for Charges is one big transaction table with all the amounts.
Out of these transactions there are many other tables I need to join with specific where clauses to classify my data. For example. I need all the charges that were written off as Charity. So from my transaction table I need to join another table where a code is equal to a few values and another columns has to be a specific value.

For reuse-ability by other report writers should I create a specific view vCharityDefinition with the codes in the view's where clause that returns the IDs that will join to the transaction table to give me the rows I need? Or maybe create that view and then another view vCharityCharges that merges the data I need from Transactions with the vCharityDefinition for all the rows. How far deep of a hierarchy is typically before it gets to confusing? Do you make these hierarchies easier to manage via naming conventions and\or use of schemas?

And what if my Charity Definition view requires a join in it with a where clause that filters both tables. Would I split ti again so no view every filters more than one table? I read soem stairways and an article or two on Functions and it looks like i should avoid those if possible along with cross apply.

Thanks,
Phil
Post #1567164
Posted Friday, May 2, 2014 2:34 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, December 5, 2014 9:21 AM
Points: 776, Visits: 337
Hi. You are asking a huge number of questions without giving us enough details to be helpful.

Specifically, we would need to know the layouts of the tables.

Speaking personally, I'm not the biggest fan of using views in the manner you propose. However, I admit this to be a personal preference and not a best practice.

Please send more details so we can get a feel for what you are trying to do.

Thanks
John
Post #1567180
Posted Friday, May 2, 2014 3:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:01 AM
Points: 47, Visits: 175
Maybe I should simplify it. Let's say you need to report on Midwest states in many reports, ETLS, or stored procedures... Would it not be easier to have a vie for Midwest states than typing out a list of states in an in list function for all those objects that would need to filter to Midwest states.
Post #1567186
Posted Friday, May 2, 2014 3:41 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 4,046, Visits: 9,202
Phillip.Putzback (5/2/2014)
Maybe I should simplify it. Let's say you need to report on Midwest states in many reports, ETLS, or stored procedures... Would it not be easier to have a vie for Midwest states than typing out a list of states in an in list function for all those objects that would need to filter to Midwest states.

How would you define the "Midwest states"? What happens if that definition changes? What happen when you want something more flexible?
Views might do the work now, but flexibility is something you should appreciate.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1567191
Posted Friday, May 2, 2014 7:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:01 AM
Points: 47, Visits: 175
I'd define the Midwest states by whatever the company defined them as. Let's say I was a franchise and I was limited so a certain area. Say Indiana, Ohio and Kentucky. I would think it would be better to set that standard in one view so all reports didn't have to recreate it. Then if someday I needed to add Illinois I would only have to change it in my vMidwestRegion and all reports, procedures.. would pick it up and not require a search for dependencies. I'm just wondering if that is the typical practice. Because currently i have to republish Stored procedures every time a customer decides they are missing some data or have too much and they throw another code that needs to be excluded\included. So the any referenced procedure gets changed in the test environment then pushed into Prod. Now we do have some report writers that use functions like the common split-list function that takes a list of values and parses it to create a table. But from what I am reading here it is probably better to join a view than a function that returns a table.
Post #1567202
Posted Saturday, May 3, 2014 4:29 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
Regardless of the exact definition of Midwest states, I'm extremely adverse to nesting views within views, or functions within views or functions within functions.

I recognize that creating a view means never having to figure out some difficult set of joins a second time, so that makes it extremely attractive to report writers. They have what they want in a single location and don't have to figure out the T-SQL to regenerate it.

But...

When you reference a view, the optimizer is forced to deal with every single object in that view. It determines which of them you are referencing, which of them it has to use to get your result, which of them are not necessary. That's all additional load on the optimizer. Throw in joining a view to a view, or calling a view from a view, and that load increases radically. What happens is that the optimizer can only try so many times to get you a good execution plan and then it gives up and goes with what it has. This is referred to as a timeout. If you're getting lots of timeouts on execution plans, you're probably also seeing really poor performance, or at least, very inconsistent performance as it finds different plans on different days.

While it is more difficult, I'd suggest writing T-SQL that's unique to each report. That will work better with the optimizer.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1567249
Posted Sunday, May 4, 2014 10:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:01 AM
Points: 47, Visits: 175
Thanks for the info. So the only way to manage frequently used filters is outside of SQL manually and then cut and paste into existing queries or new queries.
Post #1567342
Posted Sunday, May 4, 2014 12:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 6,845, Visits: 13,380
...customer decides they are missing some data or have too much and they throw another code that needs to be excluded\included...


Exclude/include values in a report usually is a scenario where lookup tables can be helpful. Using a stored procedure would allow the customer to add/remove/modify values as needed.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1567348
Posted Monday, May 5, 2014 4:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
Phillip.Putzback (5/4/2014)
Thanks for the info. So the only way to manage frequently used filters is outside of SQL manually and then cut and paste into existing queries or new queries.


When talking about parameters, then I'd say we're going to stored procedures, not functions or views.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1567461
Posted Monday, May 5, 2014 8:15 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 18, 2014 7:20 AM
Points: 313, Visits: 1,151
Joining to a lookup table is the way we've got around a similar issue, ever changing values from users. And for the example of midwest states, we have a table of all states, just have a column in the table to house the value 'midwest' then reference that in the join.
Then as you add or remove states from the group 'midwest' you don't need to change your code.
Post #1567523
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse