Take the 2-minute tour ×

We currently have a self-made entity framework that relies on a DB-indipendent ORM.

I have to build a software that batch-loads metadata in the DB for about 150 excel template (with info on cell position, cell type, formatting and more).

I can operate

  • via SQL batch (faster but less interactive)

  • via building objects in memory, processing them with LINQ queries for various integrity checks, and then committing modifications to the DB

I know that SQL is absolutely faster, but I would know... how much is it faster?

In detail, how much is a SQL query faster then a LINQ query (assuming that all needed data has been already loaded in memory by ORM) ?

share|improve this question
Somewhat related to your problem: martinfowler.com/articles/dblogic.html –  Habib Oct 31 '12 at 10:23
Does speed matter that much? Ease of maintenance should trump absolute performance in almost all cases –  gbn Oct 31 '12 at 10:27
Especially when doing joins and somewhat complex queries, I suppose SQL is expected to return in millis, while LINQ is not... SQL has indexes, LINQ has not. –  Teejay Oct 31 '12 at 10:28
@gbn Yes, speed matters that much. This is a very large load process and it should ends by 10-20 minutes at worst! –  Teejay Oct 31 '12 at 10:30
@Teejay: If you have very large datasets already existing, try using PLINQ which is much faster –  huMpty duMpty Oct 31 '12 at 10:50
show 3 more comments

closed as not constructive by podiluska, Tim Schmelter, huMpty duMpty, arshajii, Lipis Oct 31 '12 at 19:47


3 Answers

up vote 3 down vote accepted

TBH in most cases linq or SQL aren't exactly the issue. Your performance will be related to how much data you are inserting, the amount of data currently in your table and the indexes you are maintaining.

Secondly whether you need to do cross checking and/or integrity checks across multiple columns on your data. I have had situations where adding an index and rebuilding a table has taken insert time down from minutes to milliseconds, just due to bad fragmentation and lack of an algorithm.

Linq is an effective way to generate SQL for insertion and modification logic. However you will always end up with the pattern:

  1. Fetch data from database
  2. Modify data using Linq
  3. Submit changes to database.

If you have any logic you can exploit in your insertions, you may be able to use set logic to do updates in SQL. E.g. Update Customers Set KeyCustomer = 1 where Sales > 1000000. The SQL Server will process a command like this 1000s of times faster than you could ever do it with your ORM. However as @gbn has already correctly pointed out, unless you have a team full of strong SQL coders, maintenance will often trump any perf gain in the short term.

If you have to insert a significant number of records, then you should really be looking at batch loading and/or ETL via SSIS. These APIs will use smarter algorithms and perform any constraint checks in batches rather than per insert which will give you excellent performance increases. But managing an SSIS package is far more work than clicking a button in an app. These are all design decisions you will need to consider when you architect your application.

share|improve this answer
you could say that you need a team full of strong Linq developers too - or they can end up writing some dreadful linq queries. The moral is: if you're doing DB work, learn SQL. Linq is a convenience, not a replacement. –  gbjbaanb Oct 31 '12 at 12:18
add comment

Here you have some performance comparations between various ORMs and SqlDataReader: http://code.google.com/p/dapper-dot-net/ (Performance section). It's worth to mention that compiling LINQ queries may significantly improve performance: http://www.codeproject.com/Articles/38174/How-to-improve-your-LINQ-query-performance-by-5-X

share|improve this answer
Thank you for your answer. It seems that your articles are more referred to LINQ 2 SQL which I'm not using. I'm asking about pure querying performance when data is already loaded. –  Teejay Oct 31 '12 at 10:44
add comment

linq is better in keystrokes then sql..

share|improve this answer
sorry, i didn't understand your answer –  Teejay Oct 31 '12 at 10:25
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. –  user714965 Oct 31 '12 at 10:45
If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. –  dove Oct 31 '12 at 10:47
i mean yu have to write less code in linq –  Sahil Popli Oct 31 '12 at 11:55
add comment
