Does the number of columns affect query performance?

user_423704 0
Viewed 27683

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 ?

sql-server
6 Answers

Since you specified you are using the WHERE clause it will depend on how many rows are returned. If the value in your WHERE clause is UNIQUE or a PRIMARY KEY than the difference is almost non-existent. You can use EXPLAIN ANALYZE in front of your SELECT statement to view the planning time and execution time values and than you can compare your queries.

user_112044000
Related