sql server procedure optimization

Posted by stackoverflow on Stack Overflow See other posts from Stack Overflow or by stackoverflow
Published on 2010-04-04T23:48:30Z Indexed on 2010/04/04 23:53 UTC
Read the original article Hit count: 216

SQl Server 2005:

Option: 1

    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

compared to

Option: 2

    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)

When we use the second option it runs close to 50% faster. Why is this?

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server-2005