Linq to SQL one to many relationships

Question

Last couple of days i was struggling with a linq querys performance:

LinqConnectionDataContext context = new LinqConnectionDataContext();
System.Data.Linq.DataLoadOptions options = new System.Data.Linq.DataLoadOptions();
options.LoadWith<Question>(x => x.Answers);
options.LoadWith<Question>(x => x.QuestionVotes);
options.LoadWith<Answer>(x => x.AnswerVotes);
context.LoadOptions = options;
var query =( from c in context.Questions
            where c.UidUser == userGuid
            && c.Answers.Any() == true
            select new
            {
                c.Uid,
                c.Content,
                c.UidUser,
                QuestionVote = from qv in c.QuestionVotes where qv.UidQuestion == c.Uid && qv.UidUser == userGuid select new {qv.UidQuestion, qv.UidUser },
                Answer = from d in c.Answers
                         where d.UidQuestion == c.Uid
                         select new
                         {
                             d.Uid,
                             d.UidUser,
                             d.Conetent,
                             AnswerVote = from av in d.AnswerVotes where av.UidAnswer == d.Uid && av.UidUser == userGuid select new { av.UidAnswer, av.UidUser }
                         }
            }).ToList();

Query have to run through 5000 rows, and it takes up to 1 minute. How can i improve performance of this query?

Update:

Answer 1

something to get you started.

CREATE PROCEDURE GetQuestionsAndAnswers
(
    @UserGuid VARCHAR(100)
)
AS
BEGIN

SELECT 
c.Uid, 
c.Content, 
c.UidUser, 
qv.UidQuestion, 
qv.UidUser, 
av.UidAnswer, 
av.UidUser, 
av.Content,
d.Uid,
d.UidUser,
d.Content
FROM Question c
INNER JOIN QuestionVotes qv ON qv.UidQuestion = c.Uid AND qv.UidUser = @UserGuid
INNER JOIN Answers d ON d.UidQuestion = c.Uid
INNER JOIN AnswerVotes av ON av.UidAnswer = d.Uid AND av.UidUser = @UserGuid
WHERE c.UidUser = @UserGuid

END

You will already have clustered indexes on primary columns by default (just confirm this on your database side), and you would want non-clustered indexes on QuestionVote - UidUser column, AnswerVote - UidUser column, and Answer - UidQuestion column.

Also have a look here. You might want to use .AsQueryable() instead of ToList() for deferred execution

Do you ToList()?

Answer 2

Checked out the generated sql using sql-debug-visualizer and then copy the generated SQL and run it from SQL Client and see how much time it takes. If it takes near to 1 min you need to imporve performance at DB Level by adding indexing and / or stored procedure or creating views etc.

If above is not taking much time you can always create Stored Procedure and call that using LINQ to SQL.

One more recommendation is to use Entity Framework if you can change to because it is the future.