LINQ PErformance Question

[SC]

Having inherited an NHibernate-based website, I'm not sold on OR/M mapping
tools for largest sites - from what I've seen, even "tuned" nhiberate
queries result in poor performance and I haven't seen them scale well - just
one person's experience.

Has anyone used the new Linq technology on a database contained hundred of
thousands / millions of rows - can you tune Linq to retrieve data from
tuned/optimized sql queries? Just trying to understand if the OR/M stuff
(which I agree does give the developers a benefit in producing results
faster) creates a beneficial production / performance trade-off for larger
databases, or does it truly shine only with relatively small datasets.

Thanks for any opinions that people using the stuff may wish to share!

 

[Frank Rizzo]

Having inherited an NHibernate-based website, I'm not sold on OR/M mapping
tools for largest sites - from what I've seen, even "tuned" nhiberate
queries result in poor performance and I haven't seen them scale well - just
one person's experience.

Has anyone used the new Linq technology on a database contained hundred of
thousands / millions of rows - can you tune Linq to retrieve data from
tuned/optimized sql queries? Just trying to understand if the OR/M stuff
(which I agree does give the developers a benefit in producing results
faster) creates a beneficial production / performance trade-off for larger
databases, or does it truly shine only with relatively small datasets.

Thanks for any opinions that people using the stuff may wish to share!

I am curious as well. The LINQ demos that I've seen look like the old
DataGrid (or GridView) demos where you set a few properties and you got
paging, sorting, etc... Never mind that you are bringing back a million
rows every time.

 

[Jon Skeet [C# MVP]]

Having inherited an NHibernate-based website, I'm not sold on OR/M mapping
tools for largest sites - from what I've seen, even "tuned" nhiberate
queries result in poor performance and I haven't seen them scale well - just
one person's experience.

I have experience with Hibernate rather than NHibernate, but the
performance there is fantastic.

Of course, a poorly written app using ORM can be hideously inefficient,
but there's no reason why it *has* to be, at least not against modern
databases which execute prepared statements with pretty much the same
optimisation path as stored procs.

You need to identify where your fears over LINQ are, exactly:
1) CPU on the web server, working out the queries and parsing results
2) Poor queries being sent to the database
3) Something else?

I seem to remember stats recently saying that LINQ queries were about
97% as fast as using a DataReader in some benchmarks that the LINQ team
have been doing. The next thing to do is look at what queries are being
used... in my experience you *do* need to watch what SQL is being
generated by ORM tools, and occasionally you need to be quite specific
in order to make sure it's not pulling back either too much data or
doing it at the wrong time.

I haven't done any perf testing with LINQ, but I'm absolutely sure that
it's at least *designed* to be used on big databases as well as small.

 

[Jon Skeet [C# MVP]]

I am curious as well. The LINQ demos that I've seen look like the old
DataGrid (or GridView) demos where you set a few properties and you got
paging, sorting, etc... Never mind that you are bringing back a million
rows every time.

Why would you be bringing back a million rows each time? The paging,
sorting etc is being done in the SQL, not in the .NET code. Without
that "magic" LINQ wouldn't be half as interesting as it is.

 

[Frans Bouma [C# MVP]]

Having inherited an NHibernate-based website, I'm not sold on OR/M
mapping tools for largest sites - from what I've seen, even "tuned"
nhiberate queries result in poor performance and I haven't seen them
scale well - just one person's experience.

Has anyone used the new Linq technology on a database contained
hundred of thousands / millions of rows - can you tune Linq to
retrieve data from tuned/optimized sql queries? Just trying to
understand if the OR/M stuff (which I agree does give the developers
a benefit in producing results faster) creates a beneficial
production / performance trade-off for larger databases, or does it
truly shine only with relatively small datasets.

Thanks for any opinions that people using the stuff may wish to share!

O/R mapping is a scalable technique however if the developer using
the O/R mapping code is ignoring the fact that there's a database, then
there will be a problem.

In general, if the developers simply understands that the data isn't
coming from thin air but from an expensive resource called a relational
database, it is OK, as the developer will then write the C# code which
will fetch the data as optimal as possible. Take for example a fetch of
'customers' and then lazy load all orders for all these customers into
the customers. This gives poor performance as you'd better would have
used a prefetch solution with 2 queries.

Did you inspect the SQL produced by the application to see if there
were any odd things in there?

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------