title: Where to do joins - in the database server or in the application server?
body: <p>I am facing a performance (it can lead to scaling issue later on) issue at the moment. The application I am working on is quite complex and it’s running on SQL Server 2005. I need to join 6 - 7 tables to get the desired data. Each table contains more than 100,000 rows of data so far. The database schema cannot be changed (must stay as is). So I can only try to optimize as much as possible. 2 things come to my mind:</p>
  
  <ul>
  <li><p>Try not to join in database and let the application server do the filtering by using LINQ:</p>
  
  <ul>
  <li>Pros: will be able to scale easily by adding more app servers.</li>
  <li>Cons: more effort; I am not sure if responsiveness will be decreased.</li>
  </ul></li>
  <li><p>Application server stays as is and try to optimize the SQL query as much as possible (more indexes, rebuild index frequently etc):</p>
  
  <ul>
  <li>Pros: minimum effort</li>
  <li>Cons: when table records get bigger the problem will come back again</li>
  </ul></li>
  </ul>
  
  <p>Basically caching isn't an solution for me at the moment (hardware issue, hosting issues etc) and that's why I didn't bring it up originally. But I do know what the benefits of caching will bring to me and have used it many times.</p>