1

I need a help on the database access in 3-tier architecture. I am developing application in asp.net and linq. In database there are at least 9 master tables and total of 22 tables. For managing data I need to use a view for each master table.

My question is what is more convenient on runtime or for faster execution?

  1. To use page level queries (using multiple joins having at least 5-6 tables) in DataAccessLayer using linq
  2. To use view and refer them in DataAccessLayer
  3. Adding all queries to a stored procedure and then bind them all.

Which one is best practice? Is views makes page heavy while runtime?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sushant
  • 391
  • 12
  • 28

2 Answers2

2

Linq2SQL queries generally wind up as parameterized queries on your database.

There are many discussions on SO comparing the difference in performance like Are Stored Procedures more efficient, in general, than inline statements on modern RDBMS's? and Stored Procedures vs Parameterized Queries

I believe the concensus is that the benefit of the flexibility that an ORM like Linq2SQL gives generally outweighs any perceived performance loss.

IMO, LINQ2SQL will do 90% of the job just fine for most of your data access requirements, and if you have any special needs where a PROC makes more sense (e.g. a really data intensive or batch transaction), you can write one or two procs and these to your DataContext.

However, while we are at it, I wouldn't consider Linq2SQL on a new project. Why not look at Entity Framework 4+? (OP is using .NET 3.5)

Edit

If your table Foreign keys are set up correctly, when you drag your underlying tables into your Linq DBML's, you will find that you hardly ever need to join 'manually' as the ORM will handle the underlying navigation for you.

e.g. 

var myUpvotes = Users.Where(u => v.UserId == someUser)
                     .Votes.Where(v => v.Upvote == true)
                     .Count();

One concept you will need is Eager vs Lazy loading. This will definitely impact the performance of your 'joins'

Community
  • 1
  • 1
StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • thanks for reply, i never used entity framework4, is EF4 would be more beneficial in this issue? if i continue in current scenario, accessing views through stored procedure to DataAccessLayer is alright? – Sushant Aug 30 '12 at 12:00
  • @Sushant I see you've commented that you are using .NET 3.5 still. Unforunately EF4 will only work with .NET 4+, so you are stuck with LINQ. I'll update my answer. – StuartLC Aug 30 '12 at 12:02
2

I think best practice would be your #1, but you must keep an open-mind about bringing #2 or #3 to bear on the problem if the performance needs demand it. In other words, run with #1 as far as you can, but be willing to use #2 or #3 to improve those parts of the code only if/when you need it.

I've found (and agree with @nonnb) that the productivity improvement and flexibility of using Linq2SQL / ORMs makes it the right choice most of the time, but there are a few times when you need to be willing to make use of a strategic SP in your overall plan - its not an either/or decision; use both as necessary. Generally SP's will be faster, but most of the time not enough to make a difference in your application - but they should be kept in your toolset because in the right scenarios, they can make HUGE improvements when they are really needed.

E.J. Brennan
  • 45,870
  • 7
  • 88
  • 116
  • thanks @E.J. Brennan for valuable info. Right now i am using stored procedures and views in 'DataAccessLayer' . But loading time is bit slower. So I was thinking to shift myself to page level queries. Is there any way I can make binding faster? – Sushant Aug 31 '12 at 05:01