none
SQL Performance RRS feed

  • Question

  • How do I improve the performance on this query:

       var query = (from se in con.ScheduledEvents
             join dss in con.DocentShiftSchedules on se.EventId equals dss.EventId
             into a
             from b in a.DefaultIfEmpty()
             join d in con.DocentInfos on b.DocentAssigned equals d.Index
             into x
             from y in x.DefaultIfEmpty()
             where
                se.Day >= startDate.Date &&
                se.Day <= endDate.Date
             orderby se.Day,
                 se.OutReach ascending,
                 se.ShiftName,
                 se.TimeStart,
                 se.EventId,
                 b.ShiftCaptain descending,
                 y.NVFlag ascending,
                 y.LastName,
                 y.FirstName
             select new 
             {
               EventId = se.EventId,
               Day = se.Day,
               TimeStart = se.TimeStart,
               TimeEnd = se.TimeEnd,
               DisplayName = y.DisplayName,
               FirstName = y.FirstName,
               LastName = y.LastName,
               ShiftCaptain = b.ShiftCaptain,
               ShiftName = se.ShiftName,
               Comment = se.Comment,
               DocentIndex = y.Index,
               AssignmentId = b.Index
             });
    

    I'm using SQL Server 2008 R2 Express. What tools are out there to help me analyze the query?  I'm using LINQPad and have already reduced the fields in the select statement but the query still seems slow (a second or two).  The DocentShiftSchedule has nearly 15000 records, the ScheduledEvent has nearly 11000 records and the DocentInfo table has 138 records.  Is there something I can do with Keys in the database to make this faster? 

    If you need more details, let me know and I'll provide.  Any pointers would be greatly appreciated.

    Paul P.

    Tuesday, July 5, 2011 2:51 AM

All replies

  • you can use this tool: SQL Server Profiler


    Best Regards,
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, July 5, 2011 3:29 AM
  • Hi ZooGuy1492;

    If you filter before you start doing the join's a little better performance, so move the where near the start of the query.

    var query = (from se in con.ScheduledEvents
           where se.Day >= startDate.Date &&
              se.Day <= endDate.Date
    


    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, July 5, 2011 4:40 AM
  • 1) Looks like the SQL Server Profiler doesn't come with SQL Server 2008 Express

    2) Moving the Where clause to earlier in the LINQ statement doesn't seem to alter the underlying SQL statement used:

    SELECT [t2].[DisplayName] AS [DisplayName], [t2].[FirstName] AS [FirstName2], [t2].[LastName] AS [LastName2], [t1].[ShiftCaptain] AS [ShiftCaptain2], [t0].[EventId] AS [EventId2], [t0].[Day], [t0].[TimeStart], [t0].[TimeEnd], [t0].[ShiftName], [t0].[Comment], [t2].[Index] AS [DocentIndex], [t1].[Index] AS [AssignmentId]
    FROM [dbo].[ScheduledEvent] AS [t0]
    LEFT OUTER JOIN [dbo].[DocentShiftSchedule] AS [t1] ON ([t0].[EventId]) = [t1].[EventId]
    LEFT OUTER JOIN [dbo].[DocentInfo] AS [t2] ON [t1].[DocentAssigned] = ([t2].[Index])
    WHERE ([t0].[Day] >= @p0) AND ([t0].[Day] <= @p1)
    ORDER BY [t0].[Day], [t0].[OutReach], [t0].[ShiftName], [t0].[TimeStart], [t0].[EventId], [t1].[ShiftCaptain] DESC, [t2].[NVFlag], [t2].[LastName], [t2].[FirstName]
    


    3) Using the above SQL statement in SQL Server Management Studio seems to result in a respectable response time (~200ms). Is the overhead in LINQ or the memory allocation used in the SELECT part of the LINQ statement?

    Any other tips?

    Paul P.

    Tuesday, July 5, 2011 1:20 PM
  • Hi ZooGuy1492;

    To statement 3 in your last post, Linq takes the query in your code and goes through the process of converting it to T-SQL and then connects to the server and then the server executes the SQL query and then sends the results back to the caller. When Linq gets the result set from the server it then materializes each row of the result set. So yes there is some overhead in setting up the query and materializes the results. If you wish it to be faster you can use a stored procedure and have LINQ call it.

     


    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, July 5, 2011 2:56 PM
  • When comparing the times of the query using LINQPad, the time for the LINQ query using a stored procedure is 1.2seconds while the standard LINQ command is takes 0.9seconds. Doesn't look like using a stored procedure is faster.

    LINQ with Stored Procedure:

    SelectScheduledEventMinimalSP(new DateTime(2011, 6, 1), new DateTime(2011, 6, 30))

    Stored Procedure:

    ALTER PROCEDURE dbo.SelectScheduledEventMinimalSP
    	(
    	@parameter1 DateTime,
    	@parameter2 DateTime
    	)
    AS
    SELECT [t2].[DisplayName] AS [DisplayName], 
        [t2].[FirstName] AS [FirstName2], 
    	  [t2].[LastName] AS [LastName2], 
    	  [t1].[ShiftCaptain] AS [ShiftCaptain2], 
    	  [t0].[EventId] AS [EventId2], [t0].[Day], 
    	  [t0].[TimeStart], [t0].[TimeEnd], 
    	  [t0].[ShiftName], [t0].[Comment], 
    	  [t2].[Index] AS [DocentIndex], 
    	  [t1].[Index] AS [AssignmentId]
    FROM [dbo].[ScheduledEvent] AS [t0]
    LEFT OUTER JOIN [dbo].[DocentShiftSchedule] AS [t1] ON ([t0].[EventId]) = [t1].[EventId]
    LEFT OUTER JOIN [dbo].[DocentInfo] AS [t2] ON [t1].[DocentAssigned] = ([t2].[Index])
    WHERE ([t0].[Day] >= @parameter1) AND ([t0].[Day] <= @parameter2)
    ORDER BY [t0].[Day], [t0].[OutReach], [t0].[ShiftName], [t0].[TimeStart], [t0].[EventId], [t1].[ShiftCaptain] DESC, [t2].[NVFlag], [t2].[LastName], [t2].[FirstName]
    
    	RETURN
    


    More suggestions greatly appreciated.

    Paul P.

    Wednesday, July 6, 2011 1:07 AM
  • Well I don't know what to tell you SP should run faster then a normal Linq query for the reason stated.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, July 6, 2011 3:40 AM