August 31, 2014 at 10:06 am
Hi all,
I want to share a problem I had with one of my tempdbs last week. Took me some time and a lot of googling to find out the problem. I want to share it because I suppose I'm not the only one who runs into this problem.
I'm working on a 2-node cluster with 8 different instances of SQL Server 2008R2. The fact that it's a cluster is not relevant, it can also happen on a stand-alone instance.
For financial reporting, I had two views with the same columns. The first view (A) had about 840 000 records, the second view (B) had like 30 000 records. I had to present all records of both views in tabular form in Power Pivot.
Therefore I used a union all.
SELECT *
FROM ViewA
UNION ALL
SELECT *
FROM ViewB
Much to my surprise this took a very long time, then I got an error in Power Pivot telling me there was a problem with my tempdb. I checked it out and truelly, the seperate disk with the temp.mdf was full.
I tried both view seperate and it ran smoothly.
Neither of the views had any order by clause that could result in such growth.
The only difference between running them apart and as one, was the UNION ALL. I had to rewrite the statement without using UNION all. But how do you write a union all without using it?
I used the COALESCE function with a bit of a strange join clause: ON 1=0 which is ofcourse never true.
SELECT COALESCE(ViewA.Column1, ViewB.Column1) as Column1
, COALESCE(ViewA.Column2, ViewB.Column2) as Column2
, ...
FROM ViewA
FULL OUTER JOIN ViewB
ON 1=0
;
This works like a charme and it doesn't fill my tempdb.
Lesson learned: I will try to avoid the use of UNION or UNION ALL in the future.
Hope someone benefits from.
Regards,
Vera
August 31, 2014 at 10:15 am
Thanks for sharing the information. One quick question, what is the isolation level set in the connection configuration?
😎
August 31, 2014 at 11:59 pm
Isolation level: Read comitted
October 16, 2014 at 11:02 am
That is interesting and thanks for sharing. This is equivalent to the UNION ALL operator but not the UNION operator. I suppose that you can take the query, wrap it and then select distinct * from it. I wonder how that would compare to the UNION (for I try to avoid distinct * ).
----------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply