title: MSSQL Efficient return from list of Monthly Log Tables
body: <p>I am re-writing a report (in classic-asp) which connects to MSSQL and reports users usage.</p>
  <p>I have multiple &quot;Log&quot; tables for each month since we started logging, anytime a user logs in to the system, they add a log with the Type of &quot;successful login&quot;.</p>
  <p>In my report I am trying to return the latest login based on a user ID.</p>
  <p>Currently, I am using ASP to create a loop of all of the log tables in the database, to &quot;union all&quot;, and querying the unioned tables for the userid and action of &quot;successful login&quot;. This takes approximately 30 minutes to complete, since the logs have grown so large.</p>
  <p>If I limit the search to the last 3 months tables, this comes down to somewhere in the region of 5 minutes (there are 14k+ users in the database, and the report offers much more information than their last login, using SQL Execution plan manager, I have found that searching these 40+ log tables is the bottleneck.</p>
  <p>I have been considering that using If-Else statements in the SQL might be a way to <em>only</em> search multiple tables, should a row not be found, but i'm struggling to identify the logic, and as always, there is likely to be a much more efficient method of finding the results I have not even considered.</p>
  <p>using the last 3 Months</p>
  <pre><code>SELECT Name, (SELECT Top 1 LastLogin FROM (SELECT Top 1 Date As LastLogin FROM PARProjectLogs.dbo.Y2020M7 WHERE Y2020M7.UserID = Project_Users.UserID AND Type = 'Successful Login' ORDER BY Date Desc UNION ALL SELECT Top 1 Date As LastLogin FROM PARProjectLogs.dbo.Y2020M6 WHERE Y2020M6.UserID = Project_Users.UserID AND Type = 'Successful Login' ORDER BY Date Desc UNION ALL SELECT Top 1 Date As LastLogin FROM PARProjectLogs.dbo.Y2020M5 WHERE Y2020M5.UserID = Project_Users.UserID AND Type = 'Successful Login' ORDER BY Date Desc UNION ALL SELECT Top 1 Date As LastLogin FROM PARProjectLogs.dbo.Y2020M4 WHERE Y2020M4.UserID = Project_Users.UserID AND Type = 'Successful Login' ORDER BY Date Desc) As Tbl Order BY LastLogin Desc) As LastLogin FROM Project_Users ORDER BY Name
  
  </code></pre>
  <p>There are a number of databases with the title format of Y2020M1, Y2020M2, Y2020M3, Y2020M4 etc...</p>
  <p>How can I avoid searching the earlier logs if I find a result in the later logs?</p>