1

If i have very resource intensive SQL Queries to be written in DB to fetch the data. Is it recommended to use Entity Framework to fetch data using Stored Procedure calls, or should we stick to ADO.NET

Mausam Malviya
  • 55
  • 1
  • 5
  • EF can be used to run arbitrary SQL queries (and will do row set to object mapping on any results): so you can. – Richard Sep 10 '18 at 09:51

2 Answers2

1

A SQL script is a SQL script, regardless of what or who has written or generated it. If you implement a stored procedure with optimal code, then it will perform well. If you have wasteful, unneeded operations in your script, then it will do not perform well. If the script is generated with Entity Framework and it performs well, then you do not have a problem whatsoever in using it as it is. If it is not as good, then you will need a stored procedure if you want to achieve good performance.

When you wonder whether the script generated by the Entity Framework performs well, then you will need to do the following steps:

  • find out the complexity of the algorithm to be executed
  • create some test cases and based on the complexity, find out what is the expected computational time
  • if the computational time is lower than the expected time, then you will need to find out why were your expectations pessimistic in comparison to the reality you experienced with your heuristic experiments
  • otherwise, if the computational time is higher, then try to implement the same thing on your own in a stored procedure and compare the performances
  • if the computational time is around the expected time, then you do not need to do anything, EF have already done the task

I did not work with .NET and EF for a long while, but as far as I remember, it tends to gather records one-by-one in many cases, executing a separate query for each one, even when they could be gathered by a single query, which highly increases complexity.

So, in short EF is performing well in many cases, but if you experience slowness, then you will need to find out its cause, as described above. You will want to check the generated script to see whether it writes n scripts to get n records when a single script could have returned all the n needed records. Here you can see the way you can gather the generated query: How do I view the SQL generated by the Entity Framework?

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • I totally acknowledge all the pointers you mentioned. It almost answers the question for most of the scenarios. But consider a SQL query which would require 10-12 table joins or requires some Table Pivoting, or get Dense_Rank, Rank or RowNumber of a dataset (where such features are easy to use and are optimized in SQL). Do you still recommend to go for EF code using Linq, which I feel would be very cumbersome to write and get it done? – Mausam Malviya Sep 10 '18 at 11:20
  • @MausamMalviya I have written very well performing code in LINQ, which was very very long and at least 15 tables were involved and very complex calculations years ago and the code performed exceptionally well. However, I had a slightly diffferent task which was almost similar, but the small difference was something which LINQ was not able to handle well, so I had to implement the query instead. My opinion is that LINQ is good for more than 90% of the queries and when you do not foresee a performance problems, using LINQ is a good idea. – Lajos Arpad Sep 10 '18 at 12:24
  • @MausamMalviya if this optimism does not yield its fruits and LINQ was a bad choice for the given query, then I think this should not be viewed as a failure or a loss, but as an opportunity to understand a general shortcoming of LINQ which will shorten your future struggles and will make things easier on the long run. But you will need to take your time and really understand why LINQ did not perform well. At least I worked this way at the given team and I have achieved quite a lot of success there. – Lajos Arpad Sep 10 '18 at 12:26
  • @MausamMalviya to sum it up: If you know about of a query that it will not perform well due to previous experience or other information, then start with a stored procedure right away. If not, then using LINQ makes sense, as if it does not have performance deficiencies, then well written LINQ queries can be reused in very smart ways for more complex problems as parts of different LINQ queries, while a stored procedure's reusability is not as easy. – Lajos Arpad Sep 10 '18 at 12:29
  • Your approach is quite impressive. This would help in reducing skepticism(that people usually have) of using LINQ with EF for complex queries. – Mausam Malviya Sep 10 '18 at 13:06
0

The main responsibility of EntityFramework/EntityClientDataProvider is to convert LINQ-to-Entities or Entity SQL queries into a SQL query which is understood by the underlying database. It communicates with the ADO.Net data provider which in turn sends or retrieves data from the database. At the end it used ADO.Net Data Provider to communicate with the database using standard ADO.Net

So the the answer for your question is : It doesn't change in power usage for computation.

Have a look at the EF architecture diagram. EF Overview

Kailash Ravuri
  • 49
  • 6
  • I was concerned about the performance impact of using EF on complex queries. That is why i mentioned _"is it recommended"_ to use EF in such scenarios. – Mausam Malviya Sep 10 '18 at 11:10