CODEWITHSUNDEEP
sqllinq-to-sqlstored-proceduresview
Yogesh
Yogesh

Reputation: 1216

Linq to SQL - Views vs Stored Procedures

I would like to know when i am using L2S in app and getting data from multiple tables, which one to prefer sql procedure(select...join - tables) or sql view (select...join - tables).

Thanks/Yogesh

Upvotes: 2

Views: 999

Answers (2)

JonH
JonH

Reputation: 33153

You need to understand that comparing sprocs with views is not really a good / fair comparison. They do different things. It's two totally different types of objects.

If you only need data and all you are doing is some joins then use views. If you are writing some additional code and dealing with parameters / multiple statements use a sproc. Remember inside of a stored procedure you can still reference a view..so there may be cases you use both!

A lot of people like to create a single view of data that has a lot of joins and a filter (WHERE clause). They then use this view within the stored procedure to provide additional filtering based on passed in parameters. Others like to explicitly join the tables within the stored procedure.

Performance is dependant on indexes rather then direct table access versus using views. So the main thing to look out for is what indexes you have setup in your table definitions. Add indexes to fields that are very common in searches, etc.

Upvotes: 7

Hugo Migneron
Hugo Migneron

Reputation: 4907

An important difference between views and stored procedures. Standards / Performance considerations aside, this is what should guide your choice. You are getting data from multiple tables. Are you always getting the same data? If so, go with a view. Are you getting different data depending on parameters? Go with a stored proc.

This is an overly simplified analysis, but with the information we have, I think it's as good as it's gonna get...

There are many articles online that can help you make your choice. Here is an example

This article gives a lot of useful information on the performance implications of the choice between views / stored procs.

Upvotes: 3

Related Questions