Linq vs Sprocs, Pros and Cons

[Charles]

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

 

[Jeroen Mostert]

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

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.

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.

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.

 

[Stuart Ainsworth]

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.

First, thanks to Plamen for posting those links; very informative,
although I didn't see the one concern I have specifically mentioned
(the author was close, but not exact).

Second, the one concern that I have noted with LINQ (not necessarily
LINQ to SQL) is that it doesn't auto-parameterize well, which could
lead to cache pollution. In other words, when LINQ generates the
parameterized WHERE clause, it uses the absolute length of the value
being passed to the database to generate the SQL statement. Different
values= different sized parameters = new execution plan. In a high-
performance system, having multiple parameterization plans could cause
performance issues over time. This is akin to having programmers in
ADO.NET declare parameters without specifying the size; as far as I
know, though, in LINQ there is no way to change this behavior.

However, I once heard Jim Wooley remark that the data access stuff is
only 10% of the value of LINQ, but it's the part we all get hung up
on.

Stu

 

[--CELKO--]

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.

 

[Cor Ligthert[MVP]]

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

 

[Charles]

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]]

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]

Hi Eric

... if you are developing something like a call center or data
warehousing application and know your database platform will be either SQL
Server or Oracle, ...

This is probably a close match with what we are doing, and we know our
database will be SQL Server (primarily) or Oracle.

... I can see no strong and compelling case for LINQ versus
stored procedures and views.

If pressed, which do you think would be favourite? I still have this
uneasiness about Linq doing the traditional sproc stuff, and making it
hard/impossible to tune the database access, but if I have got that wrong I
don't want to unfairly criticise Linq; as I say I have not used it, but
people in our team do, and prefer it. Myself, I design and administer
databases, as well develop software, so I can and am prepared to see both
sides of the argument.

Charles

 

[Charles]

Thanks. Wise words I think.

Charles

If you are going to be doing data warehouse development, then you
absolutely
want to be implementing views and stored procedures rather than LINQ.
Not only does data warehousing involve complex SQL and aggregating GB of
data, but you typically have other consumers who need access to the data
beyond whatever front end application your're developing.
For example, what if your company hires a data analyst who needs to report
off the server using SAS, will he just reproduce from scratch SQL selects
for
all the queries and reports you've already implemented for your
application
in LINQ?
How would SAS or Excel leverage the stuff you've already spent time doing?

My advice in your case would be to implement a core set of database views
based on the design input of business analysts and case usage requirements
of
company as a whole. It should be something that can be leveraged for both
your application or more general reporting purposes, and then use those
views
as the datasource for LINQ.

 

[Mr. Arnold]

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.

That depends upon the type of Linq query being used and whether or not that
Linq query does fast/immediate or lazy-loading of data.

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.

Again, it depends of what type of Linq query is being used.

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.

It's ideal for object oriented programming, Domain Driven Design against the
model and Test Driven Design against the model, using known and proven
successful design patterns.

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.

Maybe, you should look at ADO.NET Entity Framework using Linq-to-Entities,
complied queries and ESQL.