> An analogy with web services vs. remoting just occurred to me
> that I though might be close to your heart, Frans.
>
> For many applications, the database is the bottleneck. There
> is no doubt that if you want the absolute best possible
> performance you can get out of most databases (and certainly
> out of Oracle and SQL Server), then you should use stored
> procedures. Databases keep getting better at dealing with ad
> hoc queries, but the fact is, stored procedures always do
> better, for a number of reasons. (Including one
> fundamentally unavoidable one: for any non-trivial SQL, it
> takes less network bandwidth to send a message to the
> database asking it to execute a stored procedure than it does
> to send the full query as ad hoc SQL. No amount of
> improvement in database technology will change that.) The
> difference ranges from the marginal to the substantial, but
> if you need to improve performance, anything is welcome.
stored procedures' performance fail in other area's like updates
of subsets of fields in a table, filters / sorters based on a flexible
amount of fields (very common) etc. The ascii string send which contains
a query vs. which contains a stored proc is perhaps longer, however it
also solves performance problems, I don't see a lot of difference.
> So, if the database is the bottleneck, and performance is a
> concern, ad hoc SQL would be a bad idea, and you would be
> absolutely insane to use an ORM. You should be using stored
> procedure.
haha not in a million years. You slammed me that I didn't
provide figures to back up my claims about performance of teh
xmlserializer. You were entirely right about that. Now I will smack you
this in your face ;) :
http://weblogs.asp.net/fbouma/archive/2003/05/14/7008.aspx
and the code:
http://weblogs.asp.net/fbouma/articles/7049.aspx
I know, it's not much, but it's a common scenario. You can also
add 8 stored procedures to fulfill the request demands. However add
custom sorting to the mix and you're doomed :)
In other words: if you could specify order by clauses and where
constructs using just parameters (WHERE @fieldname = @value) you would
have been totally right. The problem is: some functionality can't be
written in stored procs efficiently, which means you degrade the
performance of the application by opting for stored procedures while you
ironically did that to gain performance! :) :)
> And yet, I avoid using stored procedures. Am I wrong to do
> that?
No, it's the right option.
> I avoid them because I think they couple my business
> logic code too tightly to my database. They constrain the
> flexibility of my design. I only use them if I really have
> no other option to achieve the necessary performance, and
> I'll try really hard to find acceptable alternatives first.
> But would you advise me to change my policy, and always use
> stored procedures even when I don't absolutely need that last
> ounce of performance?
hehe, I'd severily limit my credibility when I'd advice anyone
to use stored procedures after I wrote this:
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx :)
However there are positive sides to the stored procedure aspect
though but they have nothing to do with performance or security. They
have everything to do with how you look at things. If you think an RDBMS
should provide the first 'tier' in a system, which automatically implies
it should offer some kind of API, stored procedures are the way to go.
It's defining the RDBMS' stored proc API as a service. You can also
provide that service by a tier outside teh RDBMS though, for example
with a DAL, written using an O/R mapper.
> Likewise, if the bottleneck in a system is the remote API,
> then by all means, use whatever remoting technology is fastest.
>
> Does that mean you should always use the fastest possible
> remoting technology, regardless of how it might reduce the
> flexibility of your design? That would be pretty much the
> equivalent of telling people that they should always use
> stored procedures for all their database work because stored
> procedures are fastest. And I think that would be bad
> advice. What do you think?
I see where you're heading to, that advising webservices as bad
is wrong. :) However because webservices use xml to communicate, it has
limitations, remoting doesn't have. The same with defining a stored proc
api: it has limitations, big limitations and you have to accept them
first. The problem with webservices today is that the limitations aren't
obvious. You don't know how many times I get questions about people who
want to use the rich objects of my O/R mapper in a webservice scenario.
I can't blame them, however it will never work because of the cyclic
references and the interface typed members. I fully understand how they
came with the webservice idea, as it is the only distributed technique
fully supported by vs.net and it gets a lot of hype and airtime. However
in 99% of the occasions, remoting is better in their situations because
they won't have the problems the usage of xml brings up and the clients
are .net also, consuming the objects.
It therefore would be best if vs.net would have a couple of
designers for remoting as well.
FB
===================================
This list is hosted by DevelopMentorŪ http://www.develop.com
Some .NET courses you may be interested in:
NEW! Guerrilla ASP.NET, 26 Jan 2004, in Los Angeles
http://www.develop.com/courses/gaspdotnetls
View archives and manage your subscription(s) at http://discuss.develop.com
