1

Hi I'm writing a app which has a search page and does a search on the database.

I'm wondering whether I should do this in linq or a stored procedure.

Is the performance of a stored procedure much better than that of linq to sql?

I'm thinking it would be because in order to write the linq query you need to use the datacontext to access the table on which to query. I'm imagining this in itself means that if the table is big it might become inefficient.

That is if you were using:

context.GetTable<T>();

Can any one advise me here?

Coder 2
  • 4,761
  • 12
  • 40
  • 43
  • Linq is not so fast, except if you make some tricks, check this out http://stackoverflow.com/questions/2363735/benchmark-linq2sql-subsonic2-subsonic3-any-other-ideas-to-make-them-faster – Aristos Mar 24 '11 at 02:10
  • also read this article http://peterkellner.net/2009/05/06/linq-to-sql-slow-performance-compilequery-critical/ – Aristos Mar 24 '11 at 02:13
  • The statement "Linq is very slow" - is totally incorrect. – Mitch Wheat Mar 24 '11 at 02:13
  • @Mitch I change it - ok I am still wondering about Linq... really, there are many that support it, so I maybe be have wrong. I really have speed problems with linq. So I am still searching to see why. – Aristos Mar 24 '11 at 02:16

4 Answers4

5

There is unlikely to be much difference UNLESS you encounter a situation where the TSQL produced by Linq to SQL is not optimal.

If you want absolute control over the TSQL use a stored procedure.

If speed is critical, benchmark both and also examine the TSQL produced by your Linq to SQL solution.

Also, you should be wary of pulling back entire tables (unless they are small, such as frequently accessed lookup data) across the wire in either solution.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Won't using GetTable() mean it will pull back the entire table to query on whereas if a stored procedure was used only the values that match the query will be pulled back. Ie. If the table had 2000 records would pull them all back even if the query only resulted in one value. Whereas a stored procedure would pull back one value? – Coder 2 Mar 24 '11 at 02:09
  • @Coder 2: In general, you obviously want to filter at the database, rather than bring data across the wire. Linq2Sql can do this also. – Mitch Wheat Mar 24 '11 at 02:12
  • Ok, thanks for the answer. Can you tell me though do I need to set it up to do this or does it do this automatically? – Coder 2 Mar 24 '11 at 02:15
  • Also keep in mind that stored procedures can be compiled by an engine such as Microsoft SQL Server which provides huge performance gains. – Matt Bishop Mar 24 '11 at 02:16
  • @Matt Bishop : that is no longer true: SQL Server caches query plans for ad-hoc sql as well. – Mitch Wheat Mar 24 '11 at 02:33
  • @Mitch Wheat : No doubt there is ad-hoc query plan caching, and there has been for some time, but the compilation of a stored procedure is intrinsically more efficient due to the more compact problem it is trying to address as well as additional benefits such as better analytics and overall database health reporting (watching proc calls can be quite helpful for bottleneck debugging), indexes and temp tables that can be built within (and cached), clean use of views, etc. Don't think I disagree with you though; your "absolute control" comment is correct. – Matt Bishop Mar 24 '11 at 02:46
  • The statement " but the compilation of a stored procedure is intrinsically more efficient " is also incorrect. A plan is compiled based on the current statistics; this applies to ad-hoc and TSQL stored procs. – Mitch Wheat Apr 07 '11 at 00:05
2

If the speed is so critical to you then you should go ahead and benchmark both options on a reasonable set of data. Technically I would expect the SP to be faster but it might not be that much of a difference.

ChrisWue
  • 18,612
  • 4
  • 58
  • 83
1

What does "efficient" mean to you?

I'm working on a website where sub seconds (preferably sub 500ms) is the goal. We're using Linq for search on most of our stuff. The only time we're actually using a SP is when we're using the hierarchyid and other SqlServer data types that don't exist in EF.

taylonr
  • 10,732
  • 5
  • 37
  • 66
0

GetTable probably isn't going to be that different between the two, as fundamentally it's just SELECT * FROM T. You'll see more significant gains from stored procedures in cases where the query isn't being written very optimally by Linq, or in some very high load situations were caching the execution plan makes a difference.

Benchmarking it is the best answer, but from what it looks like you're doing I don't think the difference is going to amount to much.

Tridus
  • 5,021
  • 1
  • 19
  • 19