Business Rules -> Using Lots of UDFs & Views

I am in the process of building my first "large scale" database system (after 15+ years of developing Windows Apps and Web Apps) - so I am very VERY "Green" when it comes to Database development & SQL et al.

A little context setting: I am building a multi-tier Statistical Analysis & Reporting system where the "end product" will be Reports created in Reporting Services. There are a ton of business rules that I am implementing in a Business Logic Tier (hidden from the "end user" by a Data Access Tier) comprised of SQL in the form of UDFs (scalar) and Views.

The question: I have been reading that UDFs cause a performance hit compared to things like in-line functions. Alot of the Rules (implemented as Scalar UDFs) build on each other so that the output of UDF #1 is used as input to UDF #2.

So far I am implementing the Business Logic as a hierarchy of Views (7 Views to be exact) with each view implementing multiple Rules; each Rule basically a Scalar UDF. Below is an example of what I am doing:

Example

View #1 -> Select A, B, C, funcX1(A) as ValueX1, funcY1(B, C) as ValueY1 FROM someView

Then

View #2 -> Select A, B, C, ValueX1, ValueY1, funcX2 (ValueX1) as ValueX2, funcY2(ValueY2) as ValueY2 FROM View#1

Currently I have a hierarchy of 7 views that each use UDFs to implement the Business Rules, where the value calculated from a UDF in one View is used as input to UDF in a View further down the Hierarchy.

Is there a better way of implementing all of the Rules instead of using multiple Views with a bunch of UDFs?

The "end product" dataset is then exposed as a Stored Procedure to the reports in Reporting Services.

Any help would be GREATLY appreciated.

Thanks!
- marty

You can make View #1 -> Select A, B, C, funcX1(A) as ValueX1, funcY1(B, C) as ValueY1 FROM someView
and

View #2 -> Select A, B, C, ValueX1, ValueY1, funcX2 (ValueX1) as ValueX2, funcY2(ValueY2) as ValueY2 FROM View#1

as indexed views (if your func's are deterministic ) that will improve performance

see Books Online (look for "indexed views")

To the other part I see a reccurence in building your views; another new feature of SQL 2005 is "common table expressions" that can permit creating recursive queries (look Books Online for "common table expressions" )

|||

Hi!

Well it seems you already got an answer, but here something I would like to add:

For business rules:

use 3NF design (or dw design), use constraints (fk, check constraints, uk/indexes) whenever possible

and use stored procedures as access layer (when not using dw design).

With stored procedures as layer between you highly control access and have freedom to change

your implementation (table design) behind the scenes without breaking code.

This comes escpecially useful, when you have to solve performance problems!

|||You guys Rock!

Using a CTE works like a charm! Using a CTE in a View allows me to use the output of 1 function as input to a 2nd function within the same View.

I am I'm now looking into Indexed Views to see about improving the performance of my Business Rules.

Thanks Again!