SQL vs LINQ performance [closed]

Question

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) ?

Answer 1

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.

Answer 2

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