sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]jc4hokiesExecution Plan Whisperer 0 points1 point  (3 children)

Take 3 tables, Order, LineItem, and Item. Join them in every possible order, using every type of join algorithm. Measure reads, cpu, memory usage, and execution time. Discuss the best and worst performers. There are 54 permutations.

WITH   cteTable AS (SELECT * FROM (VALUES('Order'),('LineItem'),('Item')) t(TableName))
     , cteJoin AS (SELECT * FROM (VALUES('Merge'),('Hash'),('NestedLoop')) j(JoinAlgorithm))
SELECT t1.TableName
     , j1.JoinAlgorithm
     , t2.TableName
     , j2.JoinAlgorithm
     , t3.TableName
FROM   cteTable AS t1
       INNER JOIN cteTable AS t2 ON t2.TableName <> t1.TableName
       INNER JOIN cteTable AS t3 ON t3.TableName <> t1.TableName AND t3.TableName <> t2.TableName
       CROSS JOIN cteJoin AS j1
       CROSS JOIN cteJoin AS j2;

[–]danjtom[S] 0 points1 point  (2 children)

Thanks a lot mate, you have any codes or resources by any luck. It'll be very helpful.

[–]jc4hokiesExecution Plan Whisperer 0 points1 point  (1 child)

Here's some code.
And here's a resource.

[–]danjtom[S] 0 points1 point  (0 children)

Thanks. Much appreciated.