T-SQL GetLast Record in the SQL Server Database

Question

I would like to make improvement to my existing query below.

First of all, when the tables are still relatively small, the below query ran fine and fast.
But as time goes by, the tables keep getting larger and larger everyday. Therefore, the query is running longer and longer.

I would like to find the alternative ways to improve my query below as now the query is running very long.

My objective is; I want to retrieve the most recent LoanNumber inserted into the table everyday.

Example of data in the table:
1)
LoanNumber 30495
DateSt 2022-12-18
LoanNumber 30495
DateSt 2022-12-17

2)
LoanNumber 30494
DateSt 2022-12-18

3)
LoanNumber 30489
DateSt 2022-12-16

When I run the below query, I should get the following results:

LoanNumber 30495
DateSt 2022-12-18

LoanNumber 30494
DateSt 2022-12-18

LoanNumber 30489
DateSt 2022-12-16

There is a clustered-index on LoanNumber and DateSt in each of the table.

SELECT DISTINCT   
  
li.DateSt  
,li.LoanNumber  
,LoanStatus.Status  
  
FROM [ARCUSERV].[OpenClose_Live_v1].[dbo].[1003LoanInfo] li  
INNER JOIN (SELECT loannumber, MAX(DateSt) as MaxDate  
    FROM [ARCUSERV].[OpenClose_Live_V1].[dbo].[1003LoanInfo]  
GROUP BY LoanNumber  
) latestloan on li.LoanNumber = latestloan.LoanNumber and li.DateSt = latestloan.MaxDate  
  
LEFT OUTER JOIN [ARCUSERV].[OpenClose_Live_V1].[dbo].[Misc] LoanStatus  
ON LoanStatus.LoanNumber = li.LoanNumber  
AND LoanStatus.[DateSt] = (SELECT MAX(DateST) FROM [ARCUSERV].[OpenClose_Live_V1].[dbo].[Misc] WHERE LoanNumber = li.[LoanNumber])   
  

I am wondering if there's better way to improve the above query.

Thank you in advance for your help.

Answer 1

Hi @Villa
Don't know what your tables like, but how about this query:

;WITH CTE AS  
(  
SELECT loannumber,MAX(DateSt) AS MaxDate  
FROM [ARCUSERV].[OpenClose_Live_v1].[dbo].[1003LoanInfo]   
GROUP BY LoanNumber  
)  
SELECT C.loannumber,C.MaxDate,M.Status  
FROM CTE C LEFT JOIN [ARCUSERV].[OpenClose_Live_V1].[dbo].[Misc] M   
                  ON C.loannumber=M.loannumber AND C.MaxDate=M.DateSt  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you all very much for your response.

I actually found the reason why my query is running longer.
The reason my query is taking longer to run is that some of the tables I am using do not have indexes on them.
By adding clustered-index on the tables, the query now runs much faster.

Answer 3

Check experimentally if this query is an improvement:

select distinct  
    first_value(i.DateSt) over (partition by i.LoanNumber order by i.DateSt desc),   
    i.LoanNumber,  
    first_value(m.Status) over (partition by i.LoanNumber order by i.DateSt desc)  
from [ARCUSERV].[OpenClose_Live_v1].[dbo].1003LoanInfo i  
left join [ARCUSERV].[OpenClose_Live_v1].[dbo].Misc m on m.LoanNumber = i.LoanNumber and m.DateSt = i.DateSt  
order by i.LoanNumber desc  

Answer 4

This may be better:

   ; WITH numbering AS (  
      SELECT DateSt, LoanNumber, rowno = row_number() OVER(PARTITION BY LoanNumber ORDER BY DateSt DESC)   
      FROM   [ARCUSERV].[OpenClose_Live_v1].[dbo].[1003LoanInfo]  
   )  
   SELECT n.DateSt, b.LoanNumber, LoanStatus.Status  
   FROM   numbering n  
   OUTER  APPLY (SELECT TOP 1 LoanStatus.Status  
                 FROM   [ARCUSERV].[OpenClose_Live_V1].[dbo].[Misc] LoanStatus   
                 WHERE  LoanStatus.LoanNumber = n.LoanNumber  
                 ORDER  BY LoanStatus.DateSt DESC) AS LoanStatus  
   WHERE  n.rowno = 1  

But without actual tables and data at hand it is impossible to tell. And the fact that the tables are on a linked servers raises the stakes even higher.

Also keep in mind that since I have no possibility test, I cannot vouch for that the query is correct. Test carefully.