title: sql server procedure optimization
body: <p><strong>SQl Server 2005:</strong></p>
  
  <pre>Option: 1
  <code>
      CREATE TABLE #test
        (customerid, orderdate, field1 INT, field2 INT, field3 INT)
  
      CREATE UNIQUE CLUSTERED INDEX Idx1 ON #test(customerid)
      CREATE INDEX Idx2 ON #test(field1 DESC)
      CREATE INDEX Idx3 ON #test(field2 DESC)
      CREATE INDEX Idx4 ON #test(field3 DESC)
  
      INSERT INTO #test
        (customerid, orderdate, field1 INT, field2 INT, field3 INT)
      SELECT
        customerid, orderdate, field1, field2, field3 FROM 
      ATABLERETURNING4000000ROWS
  </code>
  </pre>
  
  <p><code>compared to</code></p>
  
  <pre>Option: 2
  <code>
      CREATE TABLE #test
        (customerid, orderdate, field1 INT, field2 INT, field3 INT)
  
      INSERT INTO #test
        (customerid, orderdate, field1 INT, field2 INT, field3 INT)
      SELECT
        customerid, orderdate, field1, field2, field3 FROM 
      ATABLERETURNING4000000ROWS
  
      CREATE UNIQUE CLUSTERED INDEX Idx1 ON #test(customerid)
      CREATE INDEX Idx2 ON #test(field1 DESC)
      CREATE INDEX Idx3 ON #test(field2 DESC)
      CREATE INDEX Idx4 ON #test(field3 DESC)
  </code>
  </pre>
  
  <p>When we use the second option it runs close to 50% faster. Why is this?</p>