Re: Business objects, subset of collection

select i.invoiceid
from invoice i join payment p on i.invoiceid=p.invoiceid
group by i.invoiceid
having sum(p.amount) < i.amount and datediff(now(), i,duedate) >= 10
This is the kind of code I write every. Even though the number of
invoices and payments are very high, the queries perform within a
number of millis. The customer is happy, I am happy.
Once again, if that is all you are doing, that is CRUD/USER processing;
you are just moving piles of data back and forth between the UI and the RDB.

Maybe you can show your code performing the same task.
You want code for inventory forecasts? A Linear programs to allocate
advertising budget to various markets' media? A simulation model of
atmospheric diffusion?

No. I was asking for the OO equivalence for the SQL statement above.

What's the point? Sure I could write AAL or OOPL code to do that. But
the main point here is that one would not want to solve the problem that
way unless there was no other alternative.

The problem is to create reminders for all invoices are not payed and
due date is passed since 10 days. The solution in SQL
could look like.

insert into reminder (invoiceid, remind_date)
select i.invoiceid, now()
from invoice i join payment p on i.invoiceid=p.invoiceid
group by i.invoiceid
having sum(p.amount) < i.amount and datediff(now(), i,duedate) >= 10

If you want to solve the problem another way, you are welcome. There
are of course other alternatives. The question is if they are better,
or not.

IOW, you are providing a common generic data access query problem and
asking for an OO equivalent. But I'm talking about solving customer
specific problems, not generic data storage access problems.

This is a customer specific problem. The problem has very little to do
with storage.

The OO
solution would be tailored to a specific problem so it would very rarely
have need of that approach.

The solution (SQL code) is tailored to the specific problem.

That's kind of the point. The "algorithms" for CRUD/USER processing are
very similar because they all solve the same problem: converting RDB
views of the data to UI views and vice versa.

My example aboive has nothing to do with UI.

Caching is managed by the database. Caching is not the concern for the
application designer.
Nonsense. Every non-CRUD/USER application I have ever seen had a
client-side cache in some form. By the time the requests get to the
DBMS, it is too late. The DBMS caches just manage server access and
resources (unless you are talking about memory mapped OODBs). One caches
on the client side to minimize DB access.

Another way to elminiate the inter-process communication overhead is
to use stored procedures for "complex processing". That is why most of
TPC (www.tpc.org) implementations rely heavily on stored procedures.
Obviously you could also use a memory mapped SQL database.

The web site didn't have a whole lot to say about stored procedures (but
I didn't plow through all the papers). However, I don't think it has
much to do with the point. There are four contexts of stored procedures:

(1) Enforcing data integrity constraints on the data storage itself.
Such stored procedures are triggered directly by write operations. That
makes a whole lot of sense since data integrity is pretty important to
data storage. It also makes sense to optimize those procedures to the DB
engine.

(2) Application modules that are executed on the client side but are
stored on the server side. This is common in RAD environments because
the event code needs to be stored and managed transparently somewhere
/within/ the IDE. However, it actually adds overhead to the application
because the stored procedure needs to be read from the DB into the
client execution space rather than being there initially (e.g., as in a
compiled image).

(3) Application modules that are executed on the server side at the
request of the client. These are also common in RAD environments and
usually consist of stored queries or joins. For these to execute on the
server side all they can do is extract/update data in the DB because
there is no mechanism for interacting with the client's data structures
_as they execute_. IOW, what one is storing are CRUD/USER operations on
the stored data whose timing is dictated by the application.

(4) Application models that capture business rules and policies and are
triggered by other data accesses on the server without the application's
knowledge. This is, indeed, more efficient because they can be optimized
around the DB engine. It is also a maintainability nightmare for large
applications. Any application developer that creates such stored
procedures should have their thumbs broken.

In summary, (1) is part of the DBMS franchise; (2) actually adds
overhead; (3) is only useful for CRUD/USER processing (or to handle DB
access for the application); and (4) should be grounds for summary
dismissal. So what is the benefit of stored procedures for a
non-CRUD/USER application?

You are ignoring two other scenarios:
1. You need a user-defined function in your SQL statements. (select
age(birthdate) from employee)
2. Performance reasons. By putting "complex processing" inside the
DBMS, you can eliminate the inter-process overhead and have major
performance improvments. Browse the source code for one of the TPC
implementations, and you will see how they do it.

SQL databases excel in CRUD/USER processing, which is what I keep
saying. Conversely, they suck once one gets out of that realm.

Since your definition of CRUD/USER since to be extremly wide, I am
quite happy with this statement.

How is it extremely wide?

Any example I have given you, you have classified as CRUD/USER.

At a fundamental level CRUD/USER applications
only solve one problem: data view conversions.

The example I gave you is not about data view conversions.

They are limited to the
very fundamental tasks of creating, extracting, updating, sorting,
reporting, and deleting data stored in persistent storage.

It is like saying that the only thing a CPU do is the fundamental
tasks of adding bits.

The crucial
thing that they do not do is solve specific customer problems (i.e.,
manipulate data to produce nontrivial results).

I have showed examples of the opposite. SQL statements may solve very
specific customer problems.

//frebe
.

Relevant Pages

  • Re: Frustrated :(
    ... this SP can execute a COM ... The com creates Global Stored Procedures (Create Proc ... - all sp all re-create when the server is started ... > I just found out that the stored procedure encryption used in sql 2000 can ...
    (microsoft.public.sqlserver.security)
  • RE: sp_OACreate access
    ... It is similar to executing programs on the server. ... you should restrict access to execute the extended stored ... the EXECUTE permissions to the database user. ... and grant permissions to stored procedures to users. ...
    (microsoft.public.sqlserver.security)
  • Re: errors calling linked oleDB server stored procedures
    ... server using T-Sql directly. ... T-Sql against SQL server linked servers. ... OpenQuery uses pass through SQL to execute the remote procedure - NOT T-Sql. ... > OPENQUERY only support to excute stored procedures on SQL Server in Linked ...
    (microsoft.public.sqlserver.programming)
  • Re: Script to enumerate user databases on a remote server
    ... You could use the system stored procedures ... You use could use OSQL and execute this stored procedure in all server and ... SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • Re: Report with multiple stored procs
    ... I've been tring for awhile now to generate a report that has ... I don't think CR supports links between stored procedures. ... there'll be no logical link amongst them. ... one SP that outputs exactly what you'll need on your invoice. ...
    (microsoft.public.vb.crystal)