title: Stored Procedure Execution Plan - Data Manipulation
body: <p>I have a stored proc that processes a large amount of data (about 5m rows in this example).  The performance varies wildly.  I've had the process running in as little as 15 minutes and seen it run for as long as 4 hours.</p>
  
  <p>For maintenance, and in order to verify that the logic and processing is correct, we have the SP broken up into sections:</p>
  
  <ol>
  <li><p><code>TRUNCATE</code> and populate a work table (indexed) we can verify later with automated testing tools.</p></li>
  <li><p>Join several tables together (including some of these work tables) to product another work table</p></li>
  </ol>
  
  <p>Repeat 1 and/or 2 until a final output is produced.</p>
  
  <p>My concern is that this is a single SP and so gets an execution plan when it is first run (even <code>WITH RECOMPILE</code>).  But at that time, the work tables (permanent tables in a Work schema) are empty.</p>
  
  <p>I am concerned that, regardless of the indexing scheme, the execution plan will be poor.</p>
  
  <p>I am considering breaking up the SP and calling separate SPs from within it so that they could take advantage of a re-evaluated execution plan after the data in the work tables is built.  I have also seen reference to using EXEC to run dynamic SQL which, obviously might get a <code>RECOMPILE</code> also.</p>
  
  <p>I'm still trying to get <code>SHOWPLAN</code> permissions, so I'm flying quite blind.</p>