DavidReid
DavidReid

Reputation: 449

LinqToSQL Design Query / Worry

I wonder if somebody could point me in the right direction. I've recently started playing with LinqToSQL and love the strongly typed data objects etc.

I'm just struggling to understand the impact on database performance etc. For example, say I was developing a simple user profile page. The page shows basic information about the user, some information on their recent activity, and a list of unread notifications.

If I was developing a stored procedure for this page, I could create a single SP which returns multiple datatables covering all of the required information - resulting in a single db call.

However, using LinqToSQL, this could results in many calls - one for user info, atleast one for activity, atleast one for notifications, if I then want further info on notifications this may result in further calls - multiple db calls.

Should I be worried about the number of db calls happenning as a result of using this design pattern? Ie, are the multiple db handshakes etc going to degrade my db etc?

I'd appreciate your thoughts on this!

Thanks David

Upvotes: 2

Views: 84

Answers (2)

Jim Wooley
Jim Wooley

Reputation: 10398

LINQ to SQL can consume multiple results from a stored proc if you need to go that route. Unfortnately the designer has problems mapping them correctly, so you will probably need to create your mapping manually. See http://www.thinqlinq.com/Default/Using-LINQ-to-SQL-to-return-Multiple-Results.aspx.

You can configure LINQ to SQL to eagerly load the child records if you know that you're going to need them for every parent record. Use the DataLoadOptions and .LoadWith to configure it.

You can also project an object graph with multiple child collections in the Select clause of a LINQ query to reduce the number of DB hits that you make.

Ultimately, you need to check a number of options to determine which route is the best performance for your situation. It's not a one size fits all scenario.

Upvotes: 2

Denis Troller
Denis Troller

Reputation: 7501

Is it worst from a performance standpoint ? Yes, it should be. Multiple roundtrips are usually worse than single.

The real question is, do you mind? Is your application going to receive enough visits to warrant the added complexity of a stored procedure? Or do you value the simplicity of future modifications over raw performance?

In any case, if you need the performance, you can create a stored procedure and map it on your context. This will give you one single call, but return the data as objects Here is an article explaining a bit about that option: linq-to-sql-returning-multiple-result-sets

Upvotes: 1

Related Questions