StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can parallelism affect number of results?
text
Body
copied!<p>I have a fairly complex query that looks something like this:</p> <pre><code>create table Items(SomeOtherTableID int,SomeField int) create table SomeOtherTable(Id int,GroupID int) with cte1 as ( select SomeOtherTableID,COUNT(*) SubItemCount from Items t where t.SomeField is not null group by SomeOtherTableID ),cte2 as ( select tc.SomeOtherTableID,ROW_NUMBER() over (partition by a.GroupID order by tc.SubItemCount desc) SubItemRank from Items t inner join SomeOtherTable a on a.Id=t.SomeOtherTableID inner join cte1 tc on tc.SomeOtherTableID=t.SomeOtherTableID where t.SomeField is not null ),cte3 as ( select SomeOtherTableID from cte2 where SubItemRank=1 ) select * from cte3 t1 inner join cte3 t2 on t1.SomeOtherTableID<t2.SomeOtherTableID option (maxdop 1) </code></pre> <p>The query is such that <strong>cte3</strong> is filled with <strong>6222</strong> <em>distinct</em> results. In the final <strong>select</strong>, I am performing a cross join on <strong>cte3</strong> with itself, (so that I can compare every value in the table with every other value in the table at a later point). Notice the final line : </p> <pre><code>option (maxdop 1) </code></pre> <p>Apparently, this switches off parallelism.</p> <p>So, with <strong>6222</strong> results rows in <strong>cte3</strong>, I would expect (6222*6221)/2, or <strong>19353531</strong> results in the subsequent cross joining select, and with the final <strong>maxdop</strong> line in place, that is indeed the case.</p> <p>However, when I remove the <strong>maxdop</strong> line, the number of results jumps to <strong>19380454</strong>. I have 4 cores on my dev box.</p> <p>WTF? Can anyone explain why this is? Do I need to reconsider previous queries that cross join in this way?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload