Why execution plans are different
First query
select sum(PrinBal)
from fpc
inner join gl on gl.SnapshotKey = fpc.SnapshotKey
where fpc.SnapshotKey = 201703
The optimizer knows:
gl.SnapshotKey = fpc.SnapshotKey
; and
fpc.SnapshotKey = 201703
so it can infer:
Just as if you had written:
select sum(PrinBal)
from fpc
inner join gl on gl.SnapshotKey = fpc.SnapshotKey
where fpc.SnapshotKey = 201703
and gl.SnapshotKey = 201703
The literal value 201703 can also be used by the optimizer to determine the partition id. With both SnapshotKey
predicates (one given, one inferred) this means the optimizer knows the partition id for both tables.
Going further, with a literal value (201703) for SnapshotKey
now available on both tables, the join predicate:
gl.SnapshotKey = fpc.SnapshotKey
simplifies to:
201703 = 201703
; or simply
true
Meaning there is no join predicate at all. The result is a logical cross join. Expressing the final execution plan using the closest available T-SQL syntax, it is as if you wrote:
SELECT
CASE
WHEN SUM(Q1.c) = 0 THEN NULL
ELSE SUM(Q1.s)
END
FROM
(
SELECT c = COUNT_BIG(*), s = SUM(GL.PrinBal)
FROM dbo.gl AS GL
WHERE GL.SnapshotKey = 201703
AND $PARTITION.PF(GL.SnapshotKey) = $PARTITION.PF(201703)
) AS Q1
CROSS JOIN
(
SELECT Dummy = 1
FROM dbo.fpc AS FPC
WHERE FPC.SnapshotKey = 201703
AND $PARTITION.PF(FPC.SnapshotKey) = $PARTITION.PF(201703)
) AS Q2;
Second query
select sum(PrinBal)
from fpc
inner join gl on $Partition.PF(gl.SnapshotKey) = $Partition.PF(fpc.SnapshotKey)
where fpc.SnapshotKey = 201703
The optimizer can no longer infer anything about gl.SnapshotKey
, so the simplifications and transformations made for the first query are no longer possible.
Indeed, unless it is true that each partition holds only a single SnapshotKey
, the rewrite is not guaranteed to produce the same results.
Again, expressing the execution plan produced using the closest available T-SQL syntax:
SELECT
CASE
WHEN SUM(Q2.c) = 0 THEN NULL
ELSE SUM(Q2.s)
END
FROM
(
SELECT
Q1.PtnID,
c = COUNT_BIG(*),
s = SUM(Q1.PrinBal)
FROM
(
SELECT GL.PrinBal, PtnID = $PARTITION.PF(GL.SnapshotKey)
FROM dbo.gl AS GL
) AS Q1
GROUP BY
Q1.PtnID
) AS Q2
CROSS APPLY
(
SELECT Dummy = 1
FROM dbo.fpc AS FPC
WHERE
$PARTITION.PF(FPC.SnapshotKey) = Q2.PtnID
AND FPC.SnapshotKey = 201703
) AS Q3;
This time there is no logical cross join. Instead, there is a correlated join (an apply) on the partition id.
why the second query is faster.
This is hard to assess from the information given. Using mock data and tables based on the queries and plan image provided, I found the first query outperformed the second in every case.
The same query expressed using different syntax can often produce a different execution plan, simply because the optimizer started from a different point, and explored options in a different order before it found a suitable execution plan. Plan search is not exhaustive, and not every possible logical transformation is available, so the end result is likely to be different. As noted above, the two queries do not necessarily express the same requirement anyway (at least given the information available to the optimizer).
On a separate note, be aware that the initial columnstore implementation in SQL Server 2012 (and to a somewhat lesser extent, 2014) has many limitations, not least on the optimization side of things. You will likely get better, and more consistent, results by upgrading to a more recent release (ideally the very latest). This is particularly true if you're going to be using partitioning.
I certainly would not recommend you get into the habit of rewriting joins using $PARTITION
, except as a very last resort, and with a very deep understanding of what you are doing.
That's about all I can say without being able to see the schema or plan detail.