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 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.
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
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.
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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....
|
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)...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
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
|
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...
| |