1

I am creating asp.net web application using MVC architecture, and i am little confused about that what should i prefer between Linq and Stored Procedure to communicate with SQL server database. which can increase the performance and speed up communication between Application and database.

I am asking this question because in my previous applications i have always used stored procedure, but now i just want to know this because in my current application performance is major issue.

Friends only give the related answer of the question, because i have already seen many articles but did not find any satisfied solution.

Aakash Singh
  • 1,032
  • 8
  • 27
  • The reason the performance is faster is due to the linq driver and how the data is transferred between the Net application and the SQL database. Using a stored procedure does not change the way data is transferred and normally won't affect the performance. – jdweng Aug 09 '18 at 06:19
  • 3
    There is no one size fits all solution. That being said, for 90% of scenarios I have seen the most valuable thing to do is a) profile to find your issues (and then fix them) b) add or remove database indexes. – mjwills Aug 09 '18 at 06:20
  • 1
    That’s question to choose LinQ or SP will not answer for your performance. It depends on your skillsets about LinQ or SP, we good at SP the performance is good, same we are good at LinQ. With many right way to do the performance is similar. I prefer LinQ with EF core in the web application for code structure and good to have a code review with everything in code. For SP for big data stuffs. – ocrenaka Aug 09 '18 at 06:25
  • 2
    [race your horses](https://ericlippert.com/2012/12/17/performance-rant/)! – Damien_The_Unbeliever Aug 09 '18 at 06:25
  • I completely agree. There is no magic bullet for performance issues. You need to understand and fix them. Having said that, putting database logic in a stored procedure has the potential to stop a lot of developer side noob mistakes like not summarising on the database side. – Nick.Mc Aug 09 '18 at 06:26
  • @ocrenaka , i am totally agree with you , but in my application facing some performance issues using linq. – Aakash Singh Aug 09 '18 at 06:28
  • 1
    Once you have solid understanding on sql query and sql engine, you would never use LINQ unless it's a toy or demo project. There is huge gap between LINQ and sql statement regarding the expressiveness on data. – qxg Aug 09 '18 at 06:34
  • `but in my application facing some performance issues using linq.` Your question doesn't state that. Nor does it explain about details about those issues. – mjwills Aug 09 '18 at 06:38
  • @qxg I would not completely agree. In many cases, LINQ is just fine. Many issues are related to, for example, improper DB indexing which moving away from LINQ won't solve. Ultimately, the OP needs to profile, race your horses and make an informed decision based on their specific context. – mjwills Aug 09 '18 at 06:39
  • 1
    _only give the related answer of the question, because i have already seen many articles but did not find any satisfied solution_. There is no answer. There might be one if you do some analysis and post an actual question – Nick.Mc Aug 09 '18 at 06:42
  • 1
    @mjwills i am just saying that some of linq quires take 6-8 seconds to load the data but when i use store procedure these query runs 2-4 seconds. – Aakash Singh Aug 09 '18 at 06:44
  • I would ask yourself why are you asking for our advice? If it is 3 times faster, just use the stored proc. I mean, I suspect strongly your LINQ query and your stored proc aren't doing the same thing - but that is a moot point. You have a solution. Just use the stored proc. If you want our advice, we need to see the `CREATE TABLE` scripts (including indexes), and the LINQ and the stored proc. Otherwise it is all conjecture. – mjwills Aug 09 '18 at 06:46

1 Answers1

-1

I have face the same issue.

I have created the asp.net web application using mvc architecture and first time i have used linq in my application but it did not increase the performance, and in some part of the application i am unable to find the data in that format that i want.

And then i had to changed all the linq queries into stored procedure.

So i think you should use stored procedure.

Aakash Singh
  • 1,032
  • 8
  • 27
  • logic should either in front (asp.net c#) or backend. Again, it depends upon your product. Some people prefer logic in stored procedure so even if UI changes, logic can be retrieved – Gauravsa Aug 09 '18 at 07:02