0

I have a similar question to this (Does the number of columns affect query performance?). However, I wanted to get a more detailed explanation. I had a suspicion and confirmed that having less columns would improve the performance.

So, I ran the following test

Setup

Both tables had 19 Million Rows and a NC Index on c1

  • Table A - 46 Columns
  • Table B - 15 Columns

Query

    SELECT TOP 2 Million  
        c1,  
        c2  
    FROM Table
  • Results
    When I ran this same query against both Tables, Table B was significantly faster. Logical Reads decreased by 53%. No physical reads on either.

Question

My best guess (based on my research) is less columns leads to more rows being stored per page. Which would lead to less pages to read from cache.

Is this correct, or is there something else that I am missing?

Community
  • 1
  • 1
James
  • 117
  • 2
  • 11

1 Answers1

0

You are right,It is advice to use only require column in sql query.As far as 46 column in Table A,you should revise it.

One Page size is 8k.

I think number of data page created or read depend upon following :

i) Number of columns in table

ii) Column size of one column :Like column is holding what xml,or nvarchar or varchar(max)

If number of column or column size is very big then data page may contain only row or worse one row may reside in more than one data pages.

iii) Number of rows return from query :

iv) Index being use in Table.

Please criticise it.

KumarHarsh
  • 5,046
  • 1
  • 18
  • 22