Ronald S. Cook <rc***@westinis.comwrote:
I've always been taught that stored procedures are better than writing SQL
in client code for a number of reasons:
- runs faster as is compiled and lives on the database server
It depends what you're doing, of course, but on many databases they'll
go through the same optimisation steps, and if you make several calls
to queries which are the same other than parameters, that optimisation
is cached.
- is the more proper tier to put it since is a data function
It depends what you're trying to do. I've seen plenty of situations
where people create stored procs which *just* call normal CRUD
operations, for no reasons other than dogma. Yes, there's the
theoretical "change the database and the procs implementation, don't
change the calling code" argument - but in my experience the change is
often widespread enough to require changes in the client code anyway.
Note that there's nothing stopping you from having a layer in your
application which is solely about data, but happens to be running in
the web server (or whatever) rather than in the DB.
I can see two primary reasons to use stored procs:
1) More finely grained security - give users access to specific stored
procs, rather than a whole table, etc.
2) When the stored proc needs to do a lot of work with the data but
doesn't actually need much of the data to come out of the other end,
thus vastly reducing network traffic.
But then I've heard that writing SQL in my client .NET code might run just
as fast? Dynamic SQL or something?
It depends on the database server, but I believe that using a cached
parameterised query tends to be as fast as a simple "just CRUD" stored
proc on most of the major database platforms these days.
And then there's LINQ on the horizon. Is it a successor to everything
previously mentioned?
Well, it's a way of dynamically creating SQL and maintaining a degree
of compile-time checking. It's another step in the ORM journey - but
ORM's been around for a while now.
See
http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx for
more details "against" stored procs.
--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog:
http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too