Could this query be written better (more efficiently)?

Transact-SQL
1

Hi all, the query below doesn't take long to run (1.4 seconds) and the query plan doesn't show any table scans or key lookups. But, it is a very frequently called query so any improvement would be very positive to overall system performance. The "Activity" table is quite large (50 million records), the "Subscription" table is moderately large (15 million records, and the "Students" table is about 5 million records. Is there anything that jumps out at anyone that could be written better? Thanks!

Sorry, not sure how to format this better...

SELECT TOP (1) [t4].[value] AS [FirstLoggedIn], [t4] .[LastLoggedIn], [t4] .[value2] AS [StudentActive], [t4] .[value3] AS [courseId], [t4] .[Email], [t4] .[Username], [t4] .[value4] AS [StudentName], [t4] .[CourseTitle] AS [Title], [t4] .[value5] AS [Pass], [t4] .[value6] AS [clientId], [t4] .[value7] AS [studentId] FROM ( SELECT ( SELECT MIN([t3].[ActivityStarted]) FROM [dbo].[Activity] AS [t3] WHERE [t3].[StudentsID] = [t0].[StudentsID] ) AS [value], [t1] .[LastLoggedIn], ( CASE WHEN [t1].[ActiveStatus] = @p0 THEN 1 WHEN NOT ([t1].[ActiveStatus] = @p0) THEN 0 ELSE NULL END) AS [value2], @p1 AS [value3], [t1] .[Email], [t1] .[Username], ([t1].[LastName] + @p2) + [t1].[FirstName] AS [value4], [t2] .[CourseTitle], ( CASE WHEN [t0].[Status] = @p3 THEN 1 WHEN NOT ([t0].[Status] = @p3) THEN 0 ELSE NULL END) AS [value5], [t1] .[ClientID] AS [value6], COALESCE([t0].[StudentsID],@p4) AS [value7], [t0] .[CourseID], [t0] .[DepartmentID], [t0] .[StudentsID], [t1] .[ClientID] FROM [dbo].[Subscription] AS [t0] LEFT OUTER JOIN [dbo].[Students] AS [t1] ON [t1].[StudentsID] = [t0].[StudentsID] LEFT OUTER JOIN [dbo].[Courses] AS [t2] ON [t2].[CourseID] = [t0].[CourseID] ) AS [t4] WHERE ([t4].[CourseID] = @p5) AND ((@p6 <= @p7) OR ([t4].[DepartmentID] = @p8)) AND ((@p9 = @p10) OR ([t4].[Username] = @p11)) AND ((@p12 = @p13) OR ([t4].[Email] = @p14)) AND ((@p15 <= @p16) OR ([t4].[StudentsID] = @p17)) AND ([t4].[ClientID] = @p18)

2

You could try getting rid of the check on Activity in the nested SELECT with something like:

SELECT TOP (1) [t5].[value] AS [FirstLoggedIn]
    ,[t4].[LastLoggedIn]
    ,[t4].[value2] AS [StudentActive]
    ,[t4].[value3] AS [courseId]
    ,[t4].[Email]
    ,[t4].[Username]
    ,[t4].[value4] AS [StudentName]
    ,[t4].[CourseTitle] AS [Title]
    ,[t4].[value5] AS [Pass]
    ,[t4].[value6] AS [clientId]
    ,COALESCE([t4].[StudentsID], @p4) AS [studentId]
FROM (
    SELECT [t1].[LastLoggedIn]
        ,(
            CASE 
                WHEN [t1].[ActiveStatus] = @p0
                    THEN 1
                WHEN NOT ([t1].[ActiveStatus] = @p0)
                    THEN 0
                ELSE NULL
                END
            ) AS [value2]
        ,@p1 AS [value3]
        ,[t1].[Email]
        ,[t1].[Username]
        ,([t1].[LastName] + @p2) + [t1].[FirstName] AS [value4]
        ,[t2].[CourseTitle]
        ,(
            CASE 
                WHEN [t0].[Status] = @p3
                    THEN 1
                WHEN NOT ([t0].[Status] = @p3)
                    THEN 0
                ELSE NULL
                END
            ) AS [value5]
        ,[t1].[ClientID] AS [value6]
        ,[t0].[CourseID]
        ,[t0].[DepartmentID]
        ,[t0].[StudentsID]
        ,[t1].[ClientID]
    FROM [dbo].[Subscription] AS [t0]
    LEFT JOIN [dbo].[Students] AS [t1] ON [t1].[StudentsID] = [t0].[StudentsID]
    LEFT JOIN [dbo].[Courses] AS [t2] ON [t2].[CourseID] = [t0].[CourseID]
    ) AS [t4]
    OUTER APPLY
    (
            SELECT MIN([t3].[ActivityStarted])
            FROM [dbo].[Activity] AS [t3]
            WHERE [t3].[StudentsID] = [t4].[StudentsID]
    ) T5 (Value)
WHERE ([t4].[CourseID] = @p5)
    AND (
        (@p6 <= @p7)
        OR ([t4].[DepartmentID] = @p8)
        )
    AND (
        (@p9 = @p10)
        OR ([t4].[Username] = @p11)
        )
    AND (
        (@p12 = @p13)
        OR ([t4].[Email] = @p14)
        )
    AND (
        (@p15 <= @p16)
        OR ([t4].[StudentsID] = @p17)
        )
    AND ([t4].[ClientID] = @p18);