Vista - Linq vs Sprocs, Pros and Cons

I should say from the outset that I haven't used Linq yet, but I know of it
and its popularity.

From a database point of view, I am thinking that the use of Linq can
obscure the underlying access to the database and prohibit query
optimisation that is key to attaining good performance. As the logic in the
high-level language becomes more complex, so can the queries generated by
Linq, to the point where speed can decline significantly. This has been my
experience.

On the other hand, as the writer of code primarily in a high-level language,
I can see the attraction of Linq in obfuscating the data accees layer and
allowing logic to be created using high-level constructs.

I would be very interested in people's views on the pros and cons of using
Linq versus creating database sprocs and using simplified high-level logic.

I have no axe to grind, and am ambivalent about the two approaches, so
please feel free to [gently] argue for one corner or the other.

TIA

Charles

Charles wrote:OK, before we go on with this, it's necessary to point out that the
DataContext scenario usually used with LINQ has full support for
strongly-typed bindings to stored procedures. People usually frame it as an
either-or debate, but then it usually boils down to the age old "should we
use stored procedures for everything or not" debate that's been around way
before LINQ (and which never goes anywhere, because both sides know they're
right).

If you like stored procedures better than LINQ to SQL, you can use LINQ to
SQL to call stored procedures and LINQ to Objects to operate on the results,
thus combining the best of both worlds as you see fit.
You said you haven't used LINQ, so the experience you are talking about
can't be personal. I can share some horror stories with you about poor SQL
too (both bad queries and just plain bad approaches). People have to know
what they're doing; if something allows people to write down bad things more
easily, it has to be balanced against how it allows people to write down
good things more easily.

LINQ is capable of producing surprisingly efficient queries, because its set
of operations maps pretty well to SQL constructs. Of course you can write
naively slow queries in LINQ, but you can do the same in SQL, and in both
cases you can rewrite as the occasion demands. Because LINQ is an
abstraction, there will inevitably be operations that will always admit a
faster implementation in SQL. This is the price of abstraction.

Now, having said this, I myself prefer stored procedures because I like the
view of the database as a repository of data functions. It's a model I'm
already familiar with, it puts a clean separation between layers and it
gives you the freedom to completely rearrange the physical structure of the
database, should it prove necessary. But it isn't always necessary, and not
everyone is an SQL wizard, and you can certainly have applications where the
LINQ model (which basically maps your tables to entity collections
one-to-one) is easier to develop and maintain.

--
J.

You may find Bob Beauchemin's series of blog posts on the topic interesting:
http://www.sqlskills.com/BLOGS/BOBB/...te-Part-1.aspx
http://sqlskills.com/BLOGS/BOBB/post...te-Part-6.aspx

--
Plamen Ratchev
http://www.SQLStudio.com

LINQ is just a more efficient and secure way of doing embedded SQL.
Assuming LINQ could actually generate optimized and reusable execution
plans, I believe that stored procedures and views promote better reusability
across an Enterprise environment. Sure, development groups can theoretically
share modules or frameworks across different projects or departments, but
that would require a level or cooperation, methodology, and tool
standardization that most often does not exist. For example, you may have a
SQL Server/.NET/LINQ team developing the call center application, a SQL
Server/Java/Crystal Reports team developing the reporting or CRM front end,
and a couple of data analysts in accounting using SQL Server / SAS / Excel.
If the data services are implemented using stored procedures or views, then
that's no problem; they all make the same database calls. However, LINQ is
something proprietary to the .NET platform, and queries written in LINQ are
proprietary to your application.

"Charles" wrote:

>> I would be very interested in people's views on the pros and cons of using Linq versus creating database stored procedures and using simplified high-level logic. I have no axe to grind, and am ambivalent about the two approaches, so please feel free to [gently] argue for one corner or the other.<<

Oh Darn! I am so much better at harsh
Do not be tempted to make everything look like a nail because your
only tool is a hammer. You wind up with a system that is too
monolithic to be maintained; and 80% or more of the cost of a system
over its lifetime is maintenance.

Let me step up to a higher level. Don't think in terms of products;
think in terms tiered architectures with high cohesion in their
modules and loose coupling between the modules within a tier and loose
coupling between the tiers themselves. This is basic software
engineering.

The database engine should do all of the "database stuff"; the
reporting tier should handle all reports; the presentation layer does
its thing; etc. When you move from one product to another for a tier,
then you should not worry about the other tiers.

Yes, I know that migration can be a bit more work than swapping out
printers in the hardware configuration. But if you had high cohesion
and loose coupling as initial design goals you will be amazed at what
you can do. I have FORTRAN systems that are still in use after 30
years because we wrote to DoD and FIPS standards.

This means that is okay to have a stored procedure that gets the raw
data for the end of the month Foobar report but it does not format
the dates, etc. -- strictly database stuff. Likewise the report
engine creates and distributes reports but does not modify the DB
(i.e it might send a message that the Foobar reports went out, but
don't start cleaning out the tables).

25 words or less: do the database stuff (and just the DB stuff) in
stored procedures; do the next tier in LINQ or whatever the "tool du
jour" is in your shop. Hit nails with hammers; cut wood with saws.

Charles,

In my idea is Linq definitly better then Sprocs as it is about safety and
deployment.

You probably have not yet been in a situation where the System Administrater
because he had a one day course of SQL optimezed your Sprocs.

jmo

Cor

Hi Cor

Good to hear from you again. I know exactly what you are saying.

In the situation I'm referring to, I'm actually the DBA, and it is I who
would like to optimise some sprocs. But in some parts of the project Linq is
being used so we are getting poor performance and no opportunity to optimise
the sprocs. I only meddle in my own work, and don't change other's unless a
specific problem is reported and we have agreement.

Many years ago, I was persuaded that the database is the right place for
database stuff, and the application is where business level logic is
applied. It seems that this is being turned on its head, and the database is
becoming seen as a simple repository, and no more. Otherwise good software
engineers who have little experience of good database design and practice
create an inefficient design based on a course they once did where they
learned to talk about normal forms, and then retreat to the safety of their
chosen high-level language and assume that whatever it churns out on the
database access side is the last word.

Although that sounds like I have an axe to grind after all, I really don't.
The bottom line is I'm looking for the best way to layer a database project
now that the likes of Linq have entered the fray. I am perhaps distrtustful
of it because I can't get at the SQL easily anymore, or perhaps because it
has been taken out of the database domain, where people who know about
databases can manage it, but I'm keen to get the full picture from people
who have wider experience than me and have well-informed views on the
subject.

Cheers

Charles


"Cor Ligthert[MVP]" <Notmyfirstname@xxxxxx> wrote in message
news:uo8ufTinJHA.5832@xxxxxx

Charles,

For me Linq is a query language and the datacontext from Linq extends that
to a level where you can after a querry very simple update too (see it as in
the recordset time). However Linq to SQL is in my idea not the addition to
make large multilayer applications.

However, as always just my opinion.

Cor

"Charles" <blank@xxxxxx> wrote in message
news:OY93kHknJHA.4912@xxxxxx