Brains still matter - silly SSD notions

learning direct from the pros, without ever having to waste time wading through what seems to be crap, what a wonderful world, Dare to dream! The audacity of hope! Yes we can!

Lets assume that the cost of a simple SELECT query has a one-time setup cost, and an incremental cost per row. So the full cost is the setup cost plus rows * incremental cost.

Suppose that the setup cost for a loop join is 10 and for a hash join is 100. Suppose the incremental cost is 4 per row for loop join, and 2 for a hash join.

Do people still remember how to plot a line given an offset and slope? If not, ask the nearest high school student.

Which is more efficient, loop or hash? at 30 rows, 60 rows?

The SQL optimizer formulas is slightly complicated but in short, excluding the source table index seek costs. For a loop join, 0.00000418 is assessed per row on the join itself, 0.0001581 CPU is assessed for each additional index seek to the innersource, an additional 0.003125 is assessed for each IO estimated for repeated inner source accesses, with a provision for accounting pages already accessed. There are other effects. In a high row count loop join, the optimizer may elect to sort the outer source, reducing the estimate IO to access the inner source.

For not large hash joins, the startup cost is 0.01776, the cost of each row depends on the number of bytes from the inner and outer sources, so its relatively cheap if hash a few columns, but more expensive if you want to hash fat columns. For a 4 byte column, it 0.000015266 per row. For larger hash joins, an IO cost may be assessed for spooling intermediate hash results to temp, this depends on memory, 32/64-bit.

I am not mistaken on loop, hash and merge join performance.

There is a lot of garbage written by less than qualified people out there, thinking the loop is good, hash is bad, based on observations, but not thinking it through. Adam: thats impressive thinking, on why a less efficient operator would be substited

to get the right hash or merge join, the table needs to have the right cluster key (or covering indexes). This usually involved propagating down grand parent keys. Consider: SELECT x FROM Orders o JOIN LineItem l ON l.OrderID = o.OrderID WHERE o.CustomerID = 1.

If CustomerID had been pushed down to LineItem, then the WHERE clause would have had a SARG for each table.

If the tables are not designed with an understanding of SQL cost structure, then no amount of inducing will get the best solution. The problem with SQL (any version) going to a hash/merge with scan is due to the Loop join cost structure. If all data is in memory, it might be better to force a loop join depending on the row count.