By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,971 Members | 907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,971 IT Pros & Developers. It's quick & easy.

Design question: Nested views and functions?

P: n/a
I am working in a project where the business model is complex enough
that many common retrieval functions become difficult to develop and
maintain in single query statements or functions.

I have found the logic is easier to implement (and later modify when
the code is no longer freshly remembered), by implementing the
processing layers in nested views, and functions that call
sub-functions (UDFs), not too unlike object based programming or
non-DBMS procedural languages. In some cases, the views and functions
are nested three deep.

So far, from a design standpoint, this practice is working very well.
Code that would be a recusive mess is reduced to two or three simpler
code blocks. With the proper indexing and query structure, performance
seems to be satisfactory (time will tell).

In MS SQL Server, is there anything which makes this practice unsound?

Examples:

CREATE VIEW vw2 AS SELECT * FROM tbl1 WHERE ...
CREATE VIEW vw3 AS SELECT * FROM vw2 WHERE ...
Application uses: SELECT * FROM vw3

-or-

CREATE FUNCTION udf2 AS SELECT * FROM tbl1 WHERE ...
CREATE FUNCTION udf3 AS SELECT * FROM udf2 WHERE ...
Application uses: SELECT udf3(param) AS value

Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
I should note that in the function examples I posted, the UDFs return
single scalar values, not result sets.

Thus, a more accurate example would be:

CREATE FUNCTION udf1(@id INT) RETURNS INT
AS SELECT SUM(col1) FROM tbl1
WHERE id = @id

CREATE FUNCTION udf2(id) RETURNS INT
AS SELECT COUNT(*) FROM udf1
WHERE col2 < udf2(id)

Application uses: SELECT udf2(id)

Note: examples do not reflect actual app logic, but simply demonstrate
a nesting example.

Jul 23 '05 #2

P: n/a
Matt (bs****@gmail.com) writes:
I am working in a project where the business model is complex enough
that many common retrieval functions become difficult to develop and
maintain in single query statements or functions.

I have found the logic is easier to implement (and later modify when
the code is no longer freshly remembered), by implementing the
processing layers in nested views, and functions that call
sub-functions (UDFs), not too unlike object based programming or
non-DBMS procedural languages. In some cases, the views and functions
are nested three deep.

So far, from a design standpoint, this practice is working very well.
Code that would be a recusive mess is reduced to two or three simpler
code blocks. With the proper indexing and query structure, performance
seems to be satisfactory (time will tell).

In MS SQL Server, is there anything which makes this practice unsound?


The performance of sclar UDFs are not always the best. There is quite
an overhead in using them (this appears to improve in SQL2005). A
UDF in the wrong place can kill a query.

This problem does not appear with table-valued functions.

One reason I'm a little skeptic to this approach, is that you develop
a set of views to use, programmers tend to choice the most complex and
versatile view, even if they need only one or two values from it. This
can bring in tables to the query that do not belong there, giving quite
an undesired overhead.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

P: n/a
On Mon, 7 Mar 2005 23:11:06 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.se> wrote:

....
One reason I'm a little skeptic to this approach, is that you develop
a set of views to use, programmers tend to choice the most complex and
versatile view, even if they need only one or two values from it. This
can bring in tables to the query that do not belong there, giving quite
an undesired overhead.


That brings to mind a useful optimization idea. What if SQL Server could
ignore M-1 outer joins when the results from the secondary table are not used
ore returned? Thus, if we SELECT a subset of fields from a view, unnecessary
parts of the view query could be skipped.
Jul 23 '05 #4

P: n/a
Steve Jorgensen (no****@nospam.nospam) writes:
That brings to mind a useful optimization idea. What if SQL Server
could ignore M-1 outer joins when the results from the secondary table
are not used ore returned? Thus, if we SELECT a subset of fields from a
view, unnecessary parts of the view query could be skipped.


I would expect the optimizer to be able to draw such conclusions.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

P: n/a
Steve Jorgensen wrote:
On Mon, 7 Mar 2005 23:11:06 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.se> wrote:
One reason I'm a little skeptic to this approach, is that you develop
a set of views to use, programmers tend to choice the most complex and
versatile view, even if they need only one or two values from it. This
can bring in tables to the query that do not belong there, giving quite
an undesired overhead.

That brings to mind a useful optimization idea. What if SQL Server could
ignore M-1 outer joins when the results from the secondary table are not used
ore returned? Thus, if we SELECT a subset of fields from a view, unnecessary
parts of the view query could be skipped.

This optimization is sometimes called "RI-join-elimination" because the
rule only applies when the join cannot produce additional rows (e.g a
cartesian product).
Also unused scalar subqueries can be dropped, or joins with queries than
provable can only produce one row (such as a TOP 1 or a COUNT() over the
whole group).

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.