April 26, 2011 at 8:09 am
I hope I'm putting this question in the right section. Does anyone know pluses and minuses of LINQ? Is it comparable to stored procs as far as performance? Are LINQ queries considered ad-hoc queries when they run?
April 26, 2011 at 8:47 am
I'll be researching this on my own. I was just hoping someone might have some additional insight or a good link I could read.
April 27, 2011 at 4:02 pm
LINQ is a neat, SQL-like syntax for working with dot.net collections (to be exact, classes that implement IEnumerable interface, that is, methods Current, MoveNext, Reset). But, it's not sql, nor t-sql, nor t-sql procedure. T_SQL deals with tables, vews etc, LINQ deals with dot.net collections.
April 28, 2011 at 12:46 pm
To follow on, Linq2SQL and its successor, EntityFramework are providers that connect SQL constructs (tables and views) to .Net collections so that you can use LINQ syntax to access SQL databases. These providers do the work of translating the LINQ query into SQL statements (along with a lot of other things).
These types of providers fall under the general title of ORM or object-relational-managers because they do just that: translating the object oriented constructs in the programming language with the relational structures in the database.
FWIW, personally, I like LINQ; it's the first thing to come down the road in a decade or so that has really had an impact on how I write programs.
Steve G.
April 28, 2011 at 12:54 pm
Thank you for the replies. Do either of you know how SQL Server interprets these queries? By that I mean, are they always considered ad-hoc? And since they're in the actual program, there is no tuning them like you can a stored proc, right? Don't you know about the security around this? With LINQ and/or Entity Framework, do the individual users need read access??
April 28, 2011 at 1:09 pm
The answer is a bit complicated. First, for a garden variety Linq2SQL or EntityFramework (EF) query, yes, it does show up as an ad-hoc query just like any other query sent from code. That's because behind the scenes, the ORM translates the LINQ query to SQL before sending it to the database. (If you think about it, it kinda has to work that way). Each ORM also has a 'style' for translating the LINQ into SQL, and it may not be quite what you expect, though I've not noted any performance issues. Your mileage may vary. 😉
You can call stored procedures using EF (and possibly Linq2SQL, but I don't recall clearly as it's been a while), but that's a bit more complicated, and you do that as more of a stored procedure call rather than using LINQ. Remember, LINQ only operates on collections in the .Net sphere, and the ORM does all the database work.
As for permissions, that has more to do with the connection settings and database security settings than with LINQ. Since LINQ just gets translated into SQL, all the usual provisions and caveats apply, including making sure you use least-privilege authorizations and verifying all inputs.
One major advantage of using an ORM like Linq2SQL or EF is that they produce SQL statements that are "safer" in that they parameterize variables automatically for you.
Steve G.
April 28, 2011 at 1:21 pm
Thank you. I was just wondering because the research I have done so far seems to indicate that the queries can be pretty bad from LINQ/EF. You can't really performance tune them either since they are in the code and not SQL. The security is not as safe as using a stored procedure (with proper security itself). LINQ/EF queries can increase network traffic. All things that I don't want happening...but this is being pushed on me. So, I'm trying to make the best of a bad situation and find out all I can about how LINQ/EF work.
April 28, 2011 at 1:30 pm
This leads directly to one of my "Great Fears"(*). ORM's are very nice and, as a developer, I like EF4 with Code First - but like VB before it, I'm afraid that ORMs will simply allow developers without any database knowledge or training to create horrifically bad implementations and then blame the database for the results of their bad practices. Basically, the developer will expect that the ORM will do all the query optimization (i.e. "thinking") for them, if in fact they are even aware that a query could possibly be optimized.
(N.B. Of course, assembler programmers said the same thing about high-level language compilers a couple of generations ago, so there you are.)
And, before anyone flames me, if there's any indictment of VB here, it's that the documentation and training that came with the language did not include anything remotely resembling 'best practices' - or even the hint that such might exist in the first place.
Steve G.
(*) Ok, mild concerns. 😉
April 28, 2011 at 1:32 pm
I think that what your hearing is a result of bad programming practices rather than a direct indictment of the ORM. I'm using EF 4 with CodeFirst and don't have performance issues. Of course, I have a clue about both C# and database performance. 😀
Steve G.
April 28, 2011 at 1:35 pm
That's great to hear! Can I ask what the applications transaction volume is daily?
April 28, 2011 at 1:44 pm
In my case the transaction volume is pretty mild. Where I do have the possibility of performance issues is that some of the queries to the database are quite complex.
What I found is that all the usual 'general rules' for database programming still apply. Don't get things twice. Make sure you have covering indexes. Check your join conditions to make sure you're doing efficient joins instead of table scans, check your "where" conditions to ensure you're not returned 10,000 rows when 10 will do, etc.
One technique that I found helpful was to intercept the queries being sent with Profiler, then run them in SSMS and look at the query plan. It's an extra step to figure out what's happening, but it's not hard.
(start shameless self promotion)
If you're using the full EF implementation, it does support stored procedures; if you're using the new EF Code First variant want to use Stored Procedures, you can look at: http://www.codeproject.com/KB/database/CodeFirstStoredProcedures.aspx
(end shameless self promotion)
Steve G.
April 28, 2011 at 1:58 pm
Thank you so much.
April 28, 2011 at 2:41 pm
Happy to help, and good luck! 😉
Steve G.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply