SEO

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

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.

Matt (bsg075@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, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

On Mon, 7 Mar 2005 23:11:06 +0000 (UTC), Erland Sommarskog
<esquel@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.

Steve Jorgensen (nospam@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, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Steve Jorgensen wrote:
> On Mon, 7 Mar 2005 23:11:06 +0000 (UTC), Erland Sommarskog
> <esquel@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