Improving performance on joins to large tables.

• cadebryant

  Ten Centuries

  Points: 1045

  More actions

  September 24, 2007 at 1:36 am

  #177936

  Comments posted to this topic are about the item Improving performance on joins to large tables.

• prasad_wickramasinghe

  Grasshopper

  Points: 17

  More actions

  June 30, 2008 at 11:47 pm

  #836445

  Here u can improve performance more by specifying column names rather than using "*". Because using * queries sys.columns system table for get the column names, which is unnecessary when we can simply specify them.

  But you have to remember to update SP when u change the table(columns).

  regards,

  http://onlinecoder.blogspot.com/

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 1, 2008 at 12:10 am

  #836447

  SQL has to do meta-data lookups whether or not * is specified as it has to find the types and the column lengths.

  The reasons to not use select * are more around only retrieving the columns that you need and not returning all of them out of laziness. It's also so that code won't break if a new clumn is added to a table. finally, select * makes creating covering indexes almost impossible.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass