473,320 Members | 1,838 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Design question: Nested views and functions?

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
5 2318
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

30
by: Christian Seberino | last post by:
How does Ruby compare to Python?? How good is DESIGN of Ruby compared to Python? Python's design is godly. I'm wondering if Ruby's is godly too. I've heard it has solid OOP design but then...
2
by: Forgone Conclusion | last post by:
Hi, I have a View that groups and sums up totals. This View is then nested within in another View and used (it needs to be done like this). What i need to do is to be able to vary the records...
12
by: Jeff Lanfield | last post by:
First of all, I apologize if coalescing is not the right term to describe my problem. I have a tree where each node has the same set of attributes (is the same entity) but child nodes should...
13
by: Bryan Parkoff | last post by:
I have created three classes according to my own design. First class is called CMain. It is the Top Class. Second class and third class are called CMemory and CMPU. They are the sub-classes....
8
by: Jef Driesen | last post by:
I'm implementing some image processing algorithms in C++. I created a class called 'image' (see declaration below), that will take care of the memory allocations and some basic (mathematical)...
5
by: Zero.NULL | last post by:
My multiple level nested corelated query is not fetching correct result. It work fine on small set of data, but fails on larger set of data. Any clue? Explaining data storing and discussing...
7
by: Niall | last post by:
I have converted a vs 2003 solution to 2005 and when I try to switch to design mode for a user control I get: Cannot switch views: Validation (Internet Explorer 6): Element 'Style' cannot be...
4
by: alacrite | last post by:
I have a class that I want to turn its contents into csv file. I want to be able to set the value of the delimiter, the name of the file it gets saved to, the path of that file, and maybe a few...
0
by: YellowFin Announcements | last post by:
Introduction Usability and relevance have been identified as the major factors preventing mass adoption of Business Intelligence applications. What we have today are traditional BI tools that...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.