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: 195
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
