CODEWITHSUNDEEP
sql-serverperformanceindexing
GabyLP
GabyLP

Reputation: 3781

sql select performance (Microsoft)

I have a very big table with many rows (50 million) and more than 500 columns. Its indexes are period and client. I need to keep for a period the client and another column (not an index). It takes too much time. So I'm trying to understand why:

If I do:

select count(*)
from table
where cd_periodo=201602

It takes less than 1 sec and returns the number 2 million.

If I select into a temp table the period it also takes no time (2 secs)

select cd_periodo
into #table
from table
where cd_periodo=201602

But if I select another column that it's not part of an index it takes more than 3 minutes.

select not_index_column
into #table
from table
where cd_periodo=201602

Why is this happening? I'm not doing any filter on the column.

Upvotes: 1

Views: 54

Answers (1)

sagi
sagi

Reputation: 40481

When you select an indexed column, the reader doesn't have to process and go into the entire table and read the entire row. The index helps the reader to select the value without having to actually get the row.

When you select a nonindexed column, the opposite of what I said happens, and the reader have to read the whole table in order to get the value from this column.

Upvotes: 2

Related Questions