CODEWITHSUNDEEP
sql-serverdatabase-performance
Dreamer
Dreamer

Reputation: 7551

Improve performance on simple query with table of 10 million rows

Consider there is a big table called.... BigTable with 10 million rows. Originally, there is no primary key so I added identity column along with primary key constraint and clustered index.

ALTER TABLE BigTable ADD id int identity(1,1) not null;
ALTER TABLE BigTable ADD CONSTRAINT pk_id primary key CLUSTERED (id);

Then I did a simple select

select id from BigTable;

SQL server took 1 mins 23 seconds return the result.

set statistics time on
 SQL Server Execution Times:
 CPU time = 10063 ms,  elapsed time = 65740 ms.

execution plan just simply showed a "Clustered Index scan" with cost of 100%
Number of Rows Read      10332000
Estimated Operator Cost  1740.89(100%)
Estimated I/O Cost       1729.52
Estimated Cpu Cost       11.3654
Estimated SubTree Cost   1740.89
Number of Executions     1

The reason I used this simple query is because I really try to rule out the performance factor from the query, and then still try to figure out: how to get this simple query return result in less than a couple second? Is it really the max performance I can get from SQL server to query on 10 million rows?

Upvotes: 1

Views: 2916

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88971

To scan all the values of any column from a clustered index requires a complete table scan. If you want to optimize for retrieving all the ids or count the rows try a non-clustered index on id or a columnstore index.

Upvotes: 1

Related Questions