title: SQL sum across large number of tables
body: <p>Our current database has data spread out in a table-per-store structure at the moment, such that there can be thousands of tables for a particular schema.</p>
  
  <p>Each table has pretty basic data (transactions) and looks like:</p>
  
  <pre><code>store1_table:
  | id | total_revenue | net_profit | date |
  | 1  | 25            | 5          |      |
  | 2  | 36            | 10         |      |
  | 3  | 20            | 7          |      |
  </code></pre>
  
  <p>I'm trying to get the total sales for all stores, over a specific time period.</p>
  
  <p>For a single store the performance is quite reasonable, but when I try to query the total across all the tables (hundreds, or maybe thousands), the performance slows down significantly.</p>
  
  <p>For the SQL query I'm using SQLAlchemy, but it's generating a reasonable simple query e.g.:</p>
  
  <pre><code>viewport = union_all(*hundredsOfTables)
  session.query(func.sum(viewport.c.net_profit))
  </code></pre>
  
  <p>Is it the UNION ALL that is causing the performance issue, the structure of our database, or is there a better approach to retrieving this sum of all tables' netprofit?</p>