Query part of union all performs poorly if split into pieces

GokhanVarol 2013-06-08 22:33:44

alt textThe attached zip file has 10 query plans, basically I ran every query twice and named them as 0 and 1.
What I am trying to do is compare 2 tables and generate columnname(columnid), leftvalue, rightvalue for changes and in case one side was missing generate only 1 row showing -1/LMISS/, -2/RMISS/ in the columnid.
One issue is I need to have a mapping table since some PclId could have been changed between two tables (like the county decided to change the houses pclid from AAXX to AAXX01 for example), in that case we still need to match AAXX in one table to AAXX01 in the other table.
The query syntax is simplified below, basically since the table has 209 columns and 4 of them is primary key columns the query text gets quite large. I aliased the table names in the query.
When I ran the query in union all serially it took 259 seconds, that was not good since when I do not have a mapping table and the query is simpler this takes 70 seconds or so. I then forces the query to run in serial, which took 94 seconds, pretty good. I though if I split the query into two steps instead of using a UNION ALL it should improve, but it did not. When I ran two step query serial it took 531 second when I ran the two step query in parallel it took 454 seconds.
I cannot understand why splitted 2 statements would perform poorly like this.
Any ideas?
Thank you

CREATE TABLE [Stats].[ParcelMapping](
    [NewPclId] [varchar](45) NOT NULL,
    [OldPclId] [varchar](45) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [NewPclId] ASC
)
UNIQUE NONCLUSTERED 
(
    [OldPclId] ASC
)
)
ALTER TABLE [Stats].[ParcelMapping]  WITH CHECK ADD CHECK  (([NewPclId]<>[OldPclId]))
 
 
[SELECT m.[NewPclId] AS [PclId], m.[NewPclSeqNbr] AS [PclSeqNbr], d.ColumnId, d.LeftValue, d.RightValue
FROM KEYMAPPINGTABLE m
LEFT OUTER JOIN LEFTTABLE l ON l.PclId = m.OldPclId
LEFT OUTER JOIN RIGHTTABLE r ON r.PclId = m.NewPclId
CROSS APPLY ( SELECT ColumnId, LeftValue, RightValue FROM (VALUES(CASE WHEN l.CntyCd IS NULL THEN -1/*<LMISS>*/ WHEN r.CntyCd IS NULL THEN -2/*<RMISS>*/ END, NULL, NULL),
(CASE WHEN l.PclId IS NOT NULL AND r.PclId IS NOT NULL AND ((l.[AcCd] IS NULL AND r.[AcCd] IS NOT NULL) OR (l.[AcCd] IS NOT NULL AND r.[AcCd] IS NULL) OR l.[AcCd] <> r.[AcCd]) THEN 3 END, l.[AcCd], r.[AcCd] ),
(CASE WHEN l.CntyCd IS NOT NULL AND r.CntyCd IS NOT NULL AND ((l.[AdditionYYBltDt] IS NULL AND r.[AdditionYYBltDt] IS NOT NULL) OR (l.[AdditionYYBltDt] IS NOT NULL AND r.[AdditionYYBltDt] IS NULL) OR l.[AdditionYYBltDt] <> r.[AdditionYYBltDt]) THEN 8 END, CAST(l.[AdditionYYBltDt] AS VARCHAR(4000)), CAST(r.[AdditionYYBltDt] AS VARCHAR(4000)) ),
/* .... REPEAT FOR ALL COLUMNS .... */
d(ColumnId, LeftValue, RightValue)
WHERE ColumnId IS NOT NULL)d
WHERE NOT EXISTS(SELECT
l.[AcCd],
l.[AdditionEffYYBltDt],
/* .... REPEAT FOR ALL COLUMNS .... */
INTERSECT
SELECT
r.[AcCd],
r.[AdditionEffYYBltDt],
/* .... REPEAT FOR ALL COLUMNS .... */
)
 
UNION ALL
 
SELECT ISNULL(l.[PclId], r.[PclId]) AS [PclId], d.ColumnId, d.LeftValue, d.RightValue
FROM (SELECT * FROM LEFTTABLE l WHERE NOT EXISTS (SELECT 1 FROM KEYMAPPINGTABLE p WHERE p.OldPclId = l.PclId AND p.OldPclSeqNbr = l.PclSeqNbr)) l
FULL OUTER JOIN (SELECT * FROM RIGHTTABLE r (NOLOCK) WHERE  NOT EXISTS (SELECT 1 FROM KEYMAPPINGTABLE p WHERE p.NewPclId = r.PclId AND p.NewPclSeqNbr = r.PclSeqNbr)) r
ON l.[PclId] = r.[PclId]
CROSS APPLY (SELECT ColumnId, LeftValue, RightValue FROM (VALUES(CASE WHEN l.CntyCd IS NULL THEN -1/*<LMISS>*/ WHEN r.CntyCd IS NULL THEN -2/*<RMISS>*/ END, NULL, NULL),
(CASE WHEN l.CntyCd IS NOT NULL AND r.CntyCd IS NOT NULL AND ((l.[AcCd] IS NULL AND r.[AcCd] IS NOT NULL) OR (l.[AcCd] IS NOT NULL AND r.[AcCd] IS NULL) OR l.[AcCd] <> r.[AcCd]) THEN 3 END, l.[AcCd], r.[AcCd] ),
(CASE WHEN l.CntyCd IS NOT NULL AND r.CntyCd IS NOT NULL AND ((l.[AdditionYYBltDt] IS NULL AND r.[AdditionYYBltDt] IS NOT NULL) OR (l.[AdditionYYBltDt] IS NOT NULL AND r.[AdditionYYBltDt] IS NULL) OR l.[AdditionYYBltDt] <> r.[AdditionYYBltDt]) THEN 8 END, CAST(l.[AdditionYYBltDt] AS VARCHAR(4000)), CAST(r.[AdditionYYBltDt] AS VARCHAR(4000)) ),
/* .... REPEAT FOR ALL COLUMNS .... */
d(ColumnId, LeftValue, RightValue)
WHERE d.ColumnId IS NOT NULL)d
WHERE NOT EXISTS(SELECT
l.[AcCd],
l.[AdditionEffYYBltDt],
/* .... REPEAT FOR ALL COLUMNS .... */
INTERSECT
SELECT
r.[AcCd],
r.[AdditionEffYYBltDt],
/* .... REPEAT FOR ALL COLUMNS .... */
)
OPTION(RECOMPILE)
/* OR BELOW TO GET A QUERY PLAN
OPTION(RECOMPILE, QUERYTRACEON 8649)
*/][2]