Alternate SQL Server query by performance?-CodePudding

Query which I am using:

select SUM(marks) 
from Table1 
where name = ? 
  and Date = (select top 1 Date 
              from Table1 
              where name =? 
                and Date < ? 
              order by Date desc)

Table1:

id	name	marks	Date
1	abc	34	01/01/2021
2	abc	15	05/01/2021
3	abc	20	05/01/2021
4	def	34	05/01/2021
5	abc	12	10/01/2021

select sum(marks) 
from Table1 
where name ='abc' 
  and Date = (select top 1 Date 
              from Table1 
              where name = 'abc' 
                 and Date < 10/01/2021 
              order by Date desc)

Result 35

CodePudding user response：

Using RANK() would take comparatively less time:

select sum(marks)
from
   (
   select *, rank()OVER(order by date desc) as rnk
   from table1
   where name ='abc' and Date < '10/01/2021'
   ) as we
where rnk=1

Result: 35

Explanation:

Your query is using sub-query in WHERE clause which will check for each and every condition and you are filtering for name abc 2 times. Alternatively I am doing it once and feeding subquery in FROM clause that significantly saves time.

Look at the demo here with time elapsed (have made some additional dummy data to check time)