none
Need help converting SQL to Linq (sloved) now speed question. RRS feed

  • Question

  • Hi.
    I'm trying to convert this SQL to linq:
     
    SELECT TOP 25 FS.Title AS SubjectTitle, FS.SubjectID, FT.LastUpdated, FT.ThreadID, FT.Title, FT.Posts, TU.Username AS TUsername, TU.UserID AS TUserID, 
    PU.Username AS PUsername, PU.UserID AS PUserID, FP.Added, FT.Status, FT.Sticky 
    
    FROM ForumThreads AS FT 
    INNER JOIN (SELECT DISTINCT ThreadID FROM ForumPosts WHERE UserID = 10576) AS vFP2 ON vFP2.ThreadID = FT.ThreadID 
    LEFT JOIN Users AS TU ON FT.UserID = TU.UserID 
    LEFT JOIN (SELECT ThreadID, MAX(PostID) AS PostID FROM ForumPosts AS FP GROUP BY ThreadID ) AS vFP ON vFP.ThreadID = FT.ThreadID 
    LEFT JOIN ForumPosts AS FP ON vFP.PostID = FP.PostID 
    LEFT JOIN Users AS PU ON FP.UserID = PU.UserID
    INNER JOIN ForumSubjects FS ON FS.SubjectID = FT.SubjectID 
    ORDER BY LastUpdated DESC
    
     
    I've got this, but it's not producing the correct SQL:

     

    var list = (from FT in ForumThreads 
    				join posts in ForumPosts.Where(p=>p.UserID == 10576).Select(p=> p.ThreadID).Distinct() on FT.ThreadID equals posts
    				join TU in Users on FT.UserID equals TU.UserID
    					
    				let PostID = (from post in ForumPosts
    				where post.ThreadID == FT.ThreadID
    				group post by new {post.ThreadID, post.PostID} into g
    				select new {PostID = g.Max(t=> t.PostID)}).FirstOrDefault()
    
    				join post in ForumPosts on PostID.PostID equals post.PostID
    				
    				join subject in ForumSubjects on FT.SubjectID equals subject.SubjectID
    				
    				join postUser in Users on FT.LastUpdatedByUserId equals postUser.UserID 
    				where FT.SubjectID != 2
    								 orderby FT.LastUpdated descending
    								 select new
    								 {
    									 FT.Title,
    									 FT.ThreadID,
    									 FT.Status,
    									 subject.SubjectID,
    									 SubjectStatus = subject.Status,
    									 CategoryTitle = subject.Title,
    									 FT.Posts,
    									 FT.LastUpdated,
    									 TU.UserID,
    									 TU.Username,
    									 Added = post.Added,
    									 PUserID = postUser.UserID,
    									 PUsername = postUser.Username
    								 });
    

     




    Friday, April 22, 2011 1:26 AM

Answers

  • Well, got it working:
     
    var list = (from FT in ForumThreads
    				join posts in ForumPosts.Where(p=>p.UserID == 10576).Select(p=> p.ThreadID).Distinct() on FT.ThreadID equals posts
    				join TU in Users on FT.UserID equals TU.UserID
    					
    				join fp in ForumPosts on FT.ThreadID equals fp.ThreadID into postlist2
    				from postlist in postlist2.GroupBy(p=> p.ThreadID, p=> p.PostID).Select(
    				g => new {
    				g.Key,
    				PostID = g.Max(x => x)
    				}).DefaultIfEmpty()
    
    				from post in ForumPosts.Where( p=> p.PostID == postlist.PostID).Select(p=> p).DefaultIfEmpty()
    				
    				join subject in ForumSubjects on FT.SubjectID equals subject.SubjectID
    				
    				join postUser in Users on post.UserID equals postUser.UserID 
    								 orderby FT.LastUpdated descending
    								 select new
    								 {
    									 FT.Title,
    									 FT.ThreadID,
    									 FT.Status,
    									 subject.SubjectID,
    									 SubjectStatus = subject.Status,
    									 CategoryTitle = subject.Title,
    									 FT.Posts,
    									 FT.LastUpdated,
    									 TU.UserID,
    									 TU.Username,
    									 Added = post.Added,
    									 PUserID = postUser.UserID,
    									 PUsername = postUser.Username
    								 });
    								 
    
    But it's quite slow compared to raw SQL.
    The SQL takes about 0.02 sec on 25 records. Linq takes 0.23 sec.
    Is there anyway to optimize it? Or is Linq just that slower?
    Friday, April 22, 2011 3:41 PM

All replies

  • Well, got it working:
     
    var list = (from FT in ForumThreads
    				join posts in ForumPosts.Where(p=>p.UserID == 10576).Select(p=> p.ThreadID).Distinct() on FT.ThreadID equals posts
    				join TU in Users on FT.UserID equals TU.UserID
    					
    				join fp in ForumPosts on FT.ThreadID equals fp.ThreadID into postlist2
    				from postlist in postlist2.GroupBy(p=> p.ThreadID, p=> p.PostID).Select(
    				g => new {
    				g.Key,
    				PostID = g.Max(x => x)
    				}).DefaultIfEmpty()
    
    				from post in ForumPosts.Where( p=> p.PostID == postlist.PostID).Select(p=> p).DefaultIfEmpty()
    				
    				join subject in ForumSubjects on FT.SubjectID equals subject.SubjectID
    				
    				join postUser in Users on post.UserID equals postUser.UserID 
    								 orderby FT.LastUpdated descending
    								 select new
    								 {
    									 FT.Title,
    									 FT.ThreadID,
    									 FT.Status,
    									 subject.SubjectID,
    									 SubjectStatus = subject.Status,
    									 CategoryTitle = subject.Title,
    									 FT.Posts,
    									 FT.LastUpdated,
    									 TU.UserID,
    									 TU.Username,
    									 Added = post.Added,
    									 PUserID = postUser.UserID,
    									 PUsername = postUser.Username
    								 });
    								 
    
    But it's quite slow compared to raw SQL.
    The SQL takes about 0.02 sec on 25 records. Linq takes 0.23 sec.
    Is there anyway to optimize it? Or is Linq just that slower?
    Friday, April 22, 2011 3:41 PM
  • Hello bommob1,

    You should profile that with SQL Server Profiler. For complex queries I prefer to use SQLDataReader.

    Here is a benchmark performance.

    Kind regards,


    aelassas.free.fr
    Friday, April 22, 2011 4:47 PM
  • The code that LINQ generates, look like this:
     
    -- Region Parameters
    DECLARE @p0 Int = 10576
    -- EndRegion
    SELECT [t0].[Title], [t0].[ThreadID], [t0].[Status], [t8].[SubjectID], [t8].[Status] AS [SubjectStatus], [t8].[Title] AS [CategoryTitle], [t0].[Posts], [t0].[LastUpdated], [t3].[UserID], [t3].[Username], [t7].[Added] AS [Added], [t9].[UserID] AS [PUserID], [t9].[Username] AS [PUsername]
    FROM [ForumThreads] AS [t0]
    INNER JOIN (
      SELECT DISTINCT [t1].[ThreadID]
      FROM [ForumPosts] AS [t1]
      WHERE [t1].[UserID] = @p0
      ) AS [t2] ON [t0].[ThreadID] = [t2].[ThreadID]
    INNER JOIN [Users] AS [t3] ON [t0].[UserID] = [t3].[UserID]
    CROSS APPLY ((
        SELECT NULL AS [EMPTY]
        ) AS [t4]
      OUTER APPLY (
        SELECT MAX([t5].[PostID]) AS [value]
        FROM [ForumPosts] AS [t5]
        WHERE [t0].[ThreadID] = [t5].[ThreadID]
        GROUP BY [t5].[ThreadID]
        ) AS [t6])
    LEFT OUTER JOIN [ForumPosts] AS [t7] ON [t7].[PostID] = [t6].[value]
    INNER JOIN [ForumSubjects] AS [t8] ON [t0].[SubjectID] = [t8].[SubjectID]
    INNER JOIN [Users] AS [t9] ON [t7].[UserID] = [t9].[UserID]
    ORDER BY [t0].[LastUpdated] DESC
    

    Which is close til the original SQL.
    But from the link you provided, i can see that SqlDataReader would be the preferred choice.
    Friday, April 22, 2011 5:50 PM