Does the number of columns affect query performance ?

Question

CASE 1: I have a table with 30 columns and I query using 4 columns in the where clause.

CASE 2: I have a table with 6 columns and I query using 4 columns in the where clause.

What is the difference in performance in both cases?

For example i have table

table A
{
  b varchar(10),
  c varchar(10),
  d varchar(10),
  e varchar(10),
  f varchar(10),
  g varchar(10),
  h varchar(10)

}

SELECT b,c,d
FROM A
WHERE f='foo'

create table B
{
  b varchar(10),
  c varchar(10),
  d varchar(10),
  e varchar(10),
  f varchar(10)

}

SELECT b,c,d
FROM B
WHERE f='foo'

Both A And B table have same structure means only difference in number of column and column used in where condition is also same and column in select is also same. difference is that table B only have some unused column these are not being used in select and where condition in that case is there any difference in performance of both queries ?

Answer 1

The main benefit of returning fewer columns in a select is that SQL might be able to avoid page reads on the table / cluster if it can retrieve all the data from an index (indexed columns and / or included columns in the case of a covering index)

Edit : Given the OP's updated post, an updated answer is required

With no indexes at all, both queries will do table scans. The rows per page density will be higher on table B and so B will be marginally quicker as SQL will need fetch fewer pages.

However, with indices as per below

• Index on A(f) INCLUDE (b,c,d)
• Index on B(f) INCLUDE (b,c,d)

The performance should be near identical for the queries (assuming same data in both tables), given that SQL will hit the indexes which are now of similar column widths and row densities.

Answer 2

There will be no performance difference based on the column position. Now the construction of the table is a different story e.g. number of rows, indexes, number of columns etc.

The scenario you are talking about where you are comparing the position of the column in the two tables is like comparing apples to oranges almost, because there are so many different variables besides the column position.

Answer 3

Depends on width of the table (Bytes per row), how many rows in the table, and whether there are indices on the columns used by the query. No definitive answer without that info. However, the more columns in the table, chances are it is wider. But the effect of a proper index is much more significant than the effect of the table size.

Answer 4

Unless you have a very wide column set difference with no index being used (thus a table scan) you should see little difference in performance. That being said, it is always useful/benificial to return as few columns as possible to satisfy your needs. The catch here is that greater benifit can be had by returning the columns you need rather than a second database fetch for other columns.

• Get what you need
• avoid second database query on same table for same rows
• use an index on the select column(s) (WHERE clause restricter)
• restrict columns if you do not need them to enhance data server memory efficiency/paging

Answer 5

Test it and see!

There will be a performance difference, however 99% of the time you won't notice it - usually you won't even be able to detect it!

You can't even guarantee that that the table with fewer columns will be quicker - if its bothering you then try it and see.

Technical rubbish: (from the perspective of Microsoft SQL Server)

With the assumption that in all other respects (indexes, row counts, the data contained in the 6 common columns etc...) the tables are identical, then the only real difference will be that the larger table is spread over more pages on disk / in memory.

SQL server only attempts to read the data it absolutely requires, however it will always load an entire page at a time (8 KB). Even with the exact same amount data is required as the output to the query, if that data is spread over more pages then more IO is required.

That said, SQL server is incredibly efficient with its data access, and so you are very unlikely to see a noticeable impact on performance except in extreme circumstances.

Besides, it is also likely that your query will be run against the index rather than the table anyway, and so with indexes exactly the same size the change is likely to be 0.