January 12, 2016 at 2:27 pm
I'm hoping someone on this site is able to help me understand why SQL Server is having issues when joining 2 datasets together via a union when the first data set returns zero records.
What we're experiencing is that if the first query in the union returns zero records and the second query returns 1 or more records, the query does not complete (literally has run for 7+ hours without completing). However, if you flipped the order of the queries in the union where the query that returns data is first and the query that returns zero records is second, the query finishes in 2 minutes or less. Does anyone have any idea why there is such a drastic difference in query performance simply due to the order of the output?
To head off any suggestions simply to reorder the query, the circumstances are that either query on any given day can return zero, 1 or many records. Therefore, reordering which comes first will not solve this problem.
Here is the query should you need it to visualize what is occurring:
WITH cteDataSetA AS
(SELECT Field1,
'1' AS Field2
FROM Table1
WHERE
Field3 = 'D'
AND Field1 not like '[ABCD]%'
AND Field1 not like 'xyz%'
UNION
SELECT Field1, CONVERT(VARCHAR(2),Field2) AS Field2
FROM Table2
WHERE
Field3 = 'D'
AND Field1 not like '[ABCD]%'
AND Field1 not like 'xyz%'
AND Field2 < 100
)
,cteDataSetB AS
(SELECT RN = ROW_NUMBER() OVER (PARTITION BY E.Field4,A.Field1,E.Field2 ORDER BY E.Field5 desc)
,E.Field4
,A.Field1
,CONVERT(VARCHAR(2),ISNULL(Field2,'1')) as Field2
,E.Field5
FROM cteDataSetA A
LEFT JOIN Table2 D
ON A.Field1 = D.Field1
AND D.Field3='D'
LEFT JOIN Table1 E
ON D.Field1=E.Field1
AND E.Field3='D'
LEFT JOIN Table3 E
ON D.Field1 = E.Field1
AND CONVERT(VARCHAR(2),D.Field2) = E.Field2
)
,cteDataSetC AS
(SELECT DISTINCT
ERN = ROW_NUMBER() OVER (PARTITION BY A.Field4,A.Field1,A.Field2 ORDER BY E.Field5 desc)
,A.Field4
,A.Field1
,CONVERT(VARCHAR(2),ISNULL(A.Field2,'1')) as Field2
,A.Field5
FROM Table3 A
LEFT JOIN Table4 C
ON C.Field1=A.Field1
AND C.Field2=CAST(A.Field2 AS VARCHAR(2))
AND C.Field4=A.Field4
AND A.Field3='D'
LEFT JOIN Table5 E
ON E.Field1=A.Field1
AND E.Field2=A.Field2
AND A.Field3='D'
WHERE
A.Field3 = 'D'
AND A.Field1 not like '[ABCD]%'
AND A.Field1 not like 'xyz%'
AND E.Field1 IS NULL
AND c.Field1 IS NULL
)
SELECT
RN = ROW_NUMBER() OVER( PARTITION BY B.Field4,cte.Field1,B.Field2 ORDER BY B.Field5 desc),
'' as FieldDesc
,B.Field4
,cte.Field1
,CONVERT(VARCHAR(2),ISNULL(B.Field2,'1'))
FROM
cteDataSetA cte
INNER JOIN Table2 D
ON cte.Field1 = D.Field1
AND cte.Field2=D.Field2
AND D.Field3='D'
LEFT JOIN cteDataSetB B
ON D.Field1=B.Field1
AND D.Field2=B.Field2
AND B.RN = 1
WHERE
B.Field4 is not null
UNION
SELECT
RN = ROW_NUMBER() OVER (PARTITION BY C.Field4,cteDataSetB.Field1,C.Field2 ORDER BY C.Field5 desc),
'' as FieldDesc
,C.Field4
,C.Field1
,CONVERT(VARCHAR(2),ISNULL(C.Field2,'1'))
FROM
cteDataSetC C
WHERE
C.Field4 IS NOT NULL
AND C.ERN=1
Any insight into this zero record behavioral issue is greatly appreciated!
January 12, 2016 at 5:30 pm
Don't get confused into thinking that a CTE materialises the query inside it, it is just a query format structure and what you have there is a horrible mess.
I suggest that you move each CTE query out to separate queries and store the results of each one in temporary tables instead.
Also check your joins - you have declared outer joins with predicates in the WHERE clause that force them to be INNER joins, so just use inner joins for those.
You have a row_number partitioned by columns from an OUTER join table - did you really want to partition by NULLS?
I guess some mistakes may have been made when you were obfuscating the table/column names, and I hope the original query is less messy and confusing.
In summary, Divide'n'Conquer applies here.
By all means post back versions of the query as it develops if you need more pointers.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply