Select most recent data per record from multiple tables

To optimize your query for selecting the most recent data per record from multiple tables, consider the following strategies:

1. Indexing: Ensure that the ModifiedDateTime column is indexed in each table. This can significantly speed up the ordering process when using ROW_NUMBER().
2. Temporary Tables: Instead of using Common Table Expressions (CTEs), you might want to use temporary tables to store the results of the latest records from each table. This can reduce the overhead of multiple CTEs and improve performance. Example:

      SELECT Column1, Column2, ModifiedDateTime
      INTO #TempTable1
      FROM (
          SELECT Column1, Column2, ModifiedDateTime,
                 ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY ModifiedDateTime DESC) AS RowNum
          FROM dbo.Table1
      ) AS T
      WHERE RowNum = 1;
      
      SELECT Column1, Column2, Column3
      INTO #TempTable2
      FROM (
          SELECT Column1, Column2, Column3, ModifiedDateTime,
                 ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY ModifiedDateTime DESC) AS RowNum
          FROM dbo.Table2
      ) AS T
      WHERE RowNum = 1;
      
      SELECT *
      FROM #TempTable1 t1
      LEFT JOIN #TempTable2 t2 ON t1.Column1 = t2.Column1;
   

3. Batch Processing: If you have many tables to join, consider processing them in batches. This means you can join a few tables at a time and then combine the results in subsequent steps.
4. Query Optimization: Review the execution plan of your query to identify any bottlenecks. SQL Server Management Studio provides tools to analyze and optimize your queries.
5. Partitioning: If your tables are very large, consider partitioning them based on the ModifiedDateTime or other relevant columns. This can help SQL Server manage the data more efficiently.

By implementing these strategies, you should be able to improve the performance of your query significantly.

References:

• Troubleshoot slow queries affected by query optimizer timeout

It may be better to materialise the queries into temp tables like this:

; WITH CTE AS (
   SELECT Column1, Column2, 
          ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY ModifiedDateTime DESC) AS RowNum
   FROM Table1
)
SELECT * INTO #temp1 FROM CTE WHERE RowNum = 1

ALTER TABLE #temp1 ADD PRIMARY KEY (Column1)

And then you join these temp tables together.