sql server procedure optimization
- by stackoverflow
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?
