Posted by William Stacey [MVP] on 06/02/05 17:33

I still don't know what you point is. Bad code is bad code. Some people
may write bad or inefficient code, some may not. All I was saying is that
you can not just flat out assume that because you can now write stored
procs/functions in c# or VB that that will change everything. Naturally,
there is a broad range of uses for a DB. Many are single use or turn key
where the DB is only needed in the first place for the application. Maybe
they are upgrading from a simple XML file or Access or something else. On
the other end is your Enterprise DBs running on big AIX machines or
something with a staff of DBAs. I think that is more the scenario you are
talking about. In this case, then yes you need to be really careful as
other production stuff is also involved. But the DBAs need to do that
anyway and is just part of doing business. They have to assume people will
try stupid things and protect the resource. That is what ref integrity,
triggers, and so on are for. As for your Selects, people have been able to
do this from the beginning, so what is changing? If a DBA wants to tie
everything down, then allow only SPs (that the DBAs write) and no external
selects. No new issues here that I can see.

--
William Stacey [MVP]

"DA Morgan" <damorgan@psoug.org> wrote in message
news:1117720147.568210@yasure...
> William Stacey [MVP] wrote:
>> Not sure I see the issue here. Databases exist to service applications
>> (and hence a business need), not the other way around. People could do
>> selects from any .Net language/ADO.Net for years now. Bringing the CLR
>> into the db does not open up more wild selects/updates/deletes as that
>> goes. On the contrary, at least you can store and manage that code in
>> the DB and not have it spread out in various client apps. Not sure it
>> matters what current or future language you use; perf issues will always
>> have to be addressed from all tiers - that is just part of the game.
>> IMO, bringing the "bar down" does not directly equate to soft code; I
>> would, however, think the reverse is true.
>
> You may not see an issue but my disagreement is 100%.
>
> The application front-end is irrelevant. I could use one front-end today
> and another tomorrow. No issue related to security, scalability,
> performance, integrity, auditability, etc. is tied to my front-end. If I
> change my front-end tomorrow ... no big deal ... but the data stored in
> the database is the value. If that gets changed I might as well lock the
> doors and go home.
>
> The other place where I take issue with you is what I read as an
> implicit assumption that a SQL statement is a SQL statement is a SQL
> statement: Which is clearly not true. Look at it from the standpoint of
> someone whose background is VB or C#. Which of the following SQL
> statement is the one to use? And yes they are all syntactically correct
> and all produce the exact same result set (in Oracle).
>
> 1.
> SELECT srvr_id
> FROM servers
> INTERSECT
> SELECT srvr_id
> FROM serv_inst;
>
> 2.
> SELECT srvr_id
> FROM servers
> WHERE srvr_id IN (
> SELECT srvr_id
> FROM serv_inst);
>
> 3.
> SELECT srvr_id
> FROM servers
> WHERE srvr_id IN (
> SELECT i.srvr_id
> FROM serv_inst i, servers s
> WHERE i.srvr_id = s.srvr_id);
>
> 4.
> SELECT DISTINCT s.srvr_id
> FROM servers s, serv_inst i
> WHERE s.srvr_id = i.srvr_id;
>
> 5.
> SELECT DISTINCT srvr_id
> FROM servers
> WHERE srvr_id NOT IN (
> SELECT srvr_id
> FROM servers
> MINUS
> SELECT srvr_id
> FROM serv_inst);
>
> 6.
> SELECT srvr_id
> FROM servers s
> WHERE EXISTS (
> SELECT srvr_id
> FROM serv_inst i
> WHERE s.srvr_id = i.srvr_id);
>
> My bet is you went straight for #4. And it is not the best
> by a very substantial margin.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan@x.washington.edu
> (replace x with u to respond)

[Back to original message]