Decimal(18,0) or int?

• ivan.peter

  SSC Enthusiast

  Points: 181

  More actions

  May 28, 2013 at 1:06 am

  #1618868

  GilaMonster (5/27/2013)

  ---

  ivan.peter (5/27/2013)

  ---

  Have you read about In-Memory Hash Join (or this link[/url])?

  The MSDN entry several times, as well as blog posts by members of the dev team. Written a few articles on joins too.

  Whether or not a hash table for a hash join fits into memory or not has nothing to do with whether the source table for the query was in the buffer pool before the query started execution.

  The optimiser does not consider whether or not the data for the query is in the buffer pool before the query starts. In fact, it assumes that none of the data required for the query is in the buffer pool and that all IOs will be physical.

  The optimiser also doesn't consider whether a hash join will be an in-memory or will spill (as mentioned at the end of the MSDN post). That has to do with the memory grant that the query gets for execution and the actual number of rows affected. It's the execution engine that decides, based on the rows it actually gets and the memory grant it gets, whether it can perform the hash join in memory or not.

  Now I've read those articles carefully too. And this is a correction, which I've applied to my original post too. Hopefully mpradeep23 will benefit from it:

  Optimizer's choice of join type is not based on the amount of data in the tables. More generally

  GilaMonster (5/27/2013)

  ---

  The optimiser never considers whether a table is in memory or not, or can fit in memory or not as part of its optimisation.

  Thanks to GilaMonster 😎

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 28, 2013 at 2:36 am

  #1618881

  ivan.peter (5/28/2013)

  ---

  Optimizer's choice of join type is not based on the amount of data in the tables.

  Optimiser's choice of join type (nested loop, merge or hash) is based on the amount of data, not in the tables, but about that is estimated will be affected by the query. The number of rows affects the query's costing and those three joins are costed differently relative to row count. Hence why you generally get loop joins on smaller row counts and hash joins on huge row counts.

  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