title: Slow making many aggregate queries to a very large SQL Server table
body: <p>I have a custom log/transaction table that tracks my users every action within the web application and it currently has millions of records and grows by the minute. In my application I need to implement some of way of precalculating a user's activities/actions in sql to determine whether other features/actions are available to the user within the application. For one example, before a page loads, I need to check if the user viewed a page X number of times. </p>
  
  <pre><code>(SELECT COUNT(*) FROM MyLog WHERE UserID = xxx and PageID = 123)
  </code></pre>
  
  <p>I am making several similar aggregate queries with joins for checking other conditions and the performance is poor. These checks are occuring on every page request and the application can receive hundreds of requests per minute.</p>
  
  <p>I'm looking for any ideas to improve the application performance through sql and/or application code.</p>
  
  <p>This is a .NET 2.0 app and using SQL Server 2008.</p>
  
  <p>Much thanks in advance!</p>