title: SQL Server Performance With Large Query
body: <p>Hi everyone I have a couple of queries for some reports in which each query is pulling Data from 35+ tables. Each Table has almost 100K records. All the Queries are Union ALL for Example</p>
  
  <pre><code>;With CTE
  AS
  (
  Select col1, col2, col3 FROM Table1 WHERE Some_Condition
  UNION ALL 
  Select col1, col2, col3 FROM Table2 WHERE Some_Condition
  UNION ALL 
  Select col1, col2, col3 FROM Table3 WHERE Some_Condition
  UNION ALL 
  Select col1, col2, col3 FROM Table4 WHERE Some_Condition
  .
  .
  . And so on 
  )
  SELECT col1, col2, col3 FROM CTE
  ORDER BY col3 DESC
  </code></pre>
  
  <p>So far I have only tested this query on Dev Server and I can see It takes its time to get the results. All of these 35+ tables are not related with each other and this is the only way I can think of to get all the Desired Data in result set. </p>
  
  <ol>
  <li><p>Is there a better way to do this kind of query ??<br/> </p></li>
  <li><p>If this is the only way to go for this kind of query how can I
  improve the performance for this Query by making any changes if
  possible?? <br/></p></li>
  </ol>
  
  <p><strong>My Opinion</strong> <br/>
  I Dont mind having a few dirty-reads in this report. I was thinking of using <strong>Query hints</strong> <code>with nolock</code>  or <code>Transaction Isolation Level</code> set to <code>READ UNCOMMITED</code>.</p>
  
  <p>Will any of this help ???</p>
  
  <p><strong>Edit</strong><br/>
  Every Table has 5-10 Bit columns and a Corresponding Date column to each Bit Column and my condition for each SELECT Statement is something like </p>
  
  <pre><code>WHERE BitColumn = 1 AND DateColumn IS NULL 
  </code></pre>
  
  <p><strong>Suggestion By Peers</strong><br/></p>
  
  <p><strong>Filtered Index</strong></p>
  
  <pre><code>CREATE NONCLUSTERED INDEX IX_Table_Column
  ON TableName(BitColumn)
  WHERE BitColum = 1
  </code></pre>
  
  <p><strong>Filtered Index with Included Column</strong></p>
  
  <pre><code>CREATE NONCLUSTERED INDEX fIX_IX_Table_Column
  ON TableName(BitColumn)
  INCLUDE (DateColumn)
  WHERE DateColumn IS NULL
  </code></pre>
  
  <p><strong>Is this the best way to go ? or any suggestions please ???</strong></p>