CODEWITHSUNDEEP
sqlsql-serversql-server-2008database-performance
user824910
user824910

Reputation: 1117

how to speed up a clustered index scan while selecting all fields on range of rows or all the rows

I have a table 

Books(BookId, Name, ...... , PublishedYear)

I do have about 30 fields in my Books table, where BookId is the primary key (Identity column). I have about 2 million records for this table.

I know select * is evil performance killer..

I have a situation to select range of rows or all the rows having all the columns in it. 

Select * from Books;

this query takes more than 2 seconds to scan through the data page and get all the records. On checking the execution it still uses the Clustered index scan.

Obviously 2 seconds my not be that bad, however when this table has to be joined with other tables which is executed in batch is taking time over 15 minutes (There are no duplicate records though on the final result at completion as the count is matching). The join criteria is pretty simple and yields no duplication.

Excluding this table alone has the batch execution completed in sub seconds.

Is there a way to optimize this having said that I will have to select all the columns :(

Thanks in advance.

Upvotes: 1

Views: 556

Answers (1)

Stephan B
Stephan B

Reputation: 3701

I've just run a batch against my developer instance, one SELECT specifying all Columns and one using *. There is no evidence (nor should there) that there is any difference aside from the raw parsing of my input. If I remember correctly, that old saying really means: Do not SELECT columns you are not using, they use up resources without benefit.

When you try to improve performance in your code, always check your assumptions, they might only apply to some older version (of sql server etc) or other method.

Upvotes: 1

Related Questions