3

I have the problem that the following request takeing about 45 minutes to complete (Azure S2 MSSQL):

SELECT * FROM fooEmployeesView fe WHERE fe.departmentId = 'xyz'

UNION ALL

SELECT * FROM barEmployeesView be WHERE be.departmentId = 'xyz'

While each of both select statements takes less than 1 second when executed separately. Basically I am looking for an alternative for the union all statement since I don't understand why it takes so long.

Some more information about the views: Each view shows 300-500 entries total (without where clause) and typically results in 0-5 entries with where clause (there are no duplicates in both views). So really not a lot of data, so I hope there is an alternative. A Function to combine both requests would be ok.

I already spend a lot of time also looking at execution plans. Both views (fooEmployeesView and barEmployeesView) make use of a lot of joins. So I am not looking for the root cause here because I guess you would need much more information. I just think that I have two select statements that are executed quite fast and I just want to combine the results. If there is no good solution in SQL I might just make two requests in my code and combine the lists in code but if possible I would like to avoid that.

1
  • 1
    Note that while David's answer is a fine enough workaround if you really don't care, but it may be quite simple to determine the root cause of your issue just by you providing your execution plan (which you can upload to Paste The Plan of the slow UNION ALL query.
    – J.D.
    Commented Nov 11, 2021 at 17:23

1 Answer 1

5

each of both select statements takes less than 1 second when executed separately

Then a quick-and-dirty workaround might be

set nocount on;

SELECT *  into #foo 
FROM fooEmployeesView fe WHERE fe.departmentId = 'xyz'

SELECT *  into #bar 
FROM barEmployeesView be WHERE be.departmentId = 'xyz'

select * from #foo 
union all 
select * from #bar

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.