Improve perfomance of a table which have millions of data

• rajesh.r

  Newbie

  Points: 1

  More actions

  November 15, 2012 at 11:28 pm

  #284959

  I have a table which have around 30 columns, i am selecting data based on different columns

  For example an employee table with columns empid,name,email,city,country etc

  my queries are like

  select col1,col2 from employee where email = 'somevalue'

  select col1,col2 from employee where city= 'somevalue'

  select col1,col2 from employee where country = 'somevalue'

  select col1,col2 from employee where name= 'somevalue'

  Is there any option to improve the performance of above queries instead of creating separate index on the columns name,email,city,country

  What are the options to improve the performance of above queries

• BriPan

  SSCrazy

  Points: 2061

  More actions

  November 16, 2012 at 12:34 am

  #1559649

  what if you use coalesce and make one query combine all diff where columns

  like

  select * from emp

  where id=coalesce(@id,id) and name=coalesce(@name,name)

  and make one index of all combine columns (test it and see whether its working or not in execution plan and profiler)

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  November 16, 2012 at 12:42 am

  #1559652

  rajesh.r (11/15/2012)

  ---

  Is there any option to improve the performance of above queries instead of creating separate index on the columns name,email,city,country

  What are the options to improve the performance of above queries

  its not about how many indexes you create its about how much benfit you are getting performance boost from indexes and this is totally depend on how many columns selection/search will be there (it will help you to make indexes for multiple columns). BUT Yes you also need to restrict yourself to create umwanted indexes too.

  So better way is study or analyze your queries and list down combination of columns which are participateing in reterival processes then create the indexes.

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  November 16, 2012 at 12:47 am

  #1559653

  there are also other ways to improve the peorformance (miilions of rows case )

  partiton the data.

  keep the indexes on diiferent drive if possible OR use fast drives.

  normalization of data

  use stored proc instead of queries

  update the statistics and indexes regularly ETC ETC

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)