Re: Confused about proc vs. dynamic SQL vs LINQ

Ronald S. Cook <rcook@xxxxxxxxxxxx> wrote:
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/archive/2003/11/18/38178.aspx for
more details "against" stored procs.

--
Jon Skeet - <skeet@xxxxxxxxx>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
.

Relevant Pages

  • Re: Multiple Databases vs a Few
    ... There are no reasons to create a database for each client. ... > would also think that in terms of Stored Procs it must be using more ...
    (microsoft.public.sqlserver.server)
  • Re: Form quit, bypasses cancel in textboxes BeforUpdate
    ... Just about every pc database product. ... would love to find that shortcut, and the instant pain killer. ... >> have to resort to using the forms before update event). ... there is lots of reasons for the above: ...
    (microsoft.public.access.formscoding)
  • Re: SQL Server 2000 Hardware Recommendations?
    ... Are you using stored procedures or adhoc sql calls? ... > and database server as we expand our web site to have more dynamic ... Currently the database server houses all data pertinent to ...
    (microsoft.public.sqlserver.server)
  • Re: Is file is being used
    ... The file system doesn't know anything about the content of the .mdb file. ... That said, most database do support multiple users, and they do so by ... Note that even in the case of a database server supporting multiple ...
    (microsoft.public.dotnet.languages.csharp)
  • IIS/SQL Server Hardware Recommendations?
    ... and database server as we expand our web site to have more dynamic ... Currently the database server houses all data pertinent to ... under a heavy load, a sql-heavy page can take as long as 90 seconds to ...
    (microsoft.public.inetserver.iis)