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'
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.