NHibernate - Union of Three QueryOvers

Question

Hi, users of StackOverflow,

I ran into this problem. I have three QueryOvers, and each of them returns a list of candidate identifiers, which I then use to list these candidates. For this, I wrote the following code.

private IQueryOver<CandidateEntity, CandidateEntity> UnionPublicWithPrivateCandidates( IQueryOver<CandidateEntity, CandidateEntity> publicCandidates, IQueryOver<CandidateEntity, CandidateEntity> privateCandidate, IQueryOver<CandidateEntity, CandidateEntity> candidatesByUserRole) { return ActiveCandidatesQueryOver.Where(Restrictions.Disjunction() .Add(Subqueries .WhereProperty<CandidateEntity>(c => c.Id) .In((QueryOver<CandidateEntity>)publicCandidates.Select(c => c.Id))) .Add(Subqueries .WhereProperty<CandidateEntity>(c => c.Id) .In((QueryOver<CandidateEntity>)privateCandidate.Select(c => c.Id))) .Add(Subqueries .WhereProperty<CandidateEntity>(c => c.Id) .In((QueryOver<CandidateEntity>)candidatesByUserRole.Select(c => c.Id)))); } 

This returns the correct results, and the generated query looks like

 SELECT * FROM Applicants WHERE IsActive = 1 and (Id in (SELECT Id from **FirstQueryOver**) **or** Id in (SELECT Id from **SecondQueryOver**) **or** Id in (SELECT Id from **ThirdQueryOver**)) 

The problem is that it uses an "or". Because of this, the request is painfully slow.

If instead I write this:

 SELECT * FROM Applicants WHERE IsActive = 1 and (Id in (SELECT Id from **FirstQueryOver** union SELECT Id from **SecondQueryOver** union SELECT Id from **ThirdQueryOver**)) 

It ends almost instantly.

Do you have any ideas on how to reorganize my code for better performance?

Thanks Adrian.

Answer 1

I searched, but did not find anything, so I made the following hack:

 private IQueryOver<CandidateEntity, CandidateEntity> UnionPublicWithPrivateCandidates( IQueryOver<CandidateEntity, CandidateEntity> publicCandidates, IQueryOver<CandidateEntity, CandidateEntity> privateCandidate, IQueryOver<CandidateEntity, CandidateEntity> candidatesByUserRole) { var excludedQueryCandidates = QueryOver .WithSubquery.WhereNotExists(((QueryOver<CandidateEntity>)publicCandidates.Select(x => x.Id))) .WithSubquery.WhereNotExists((QueryOver<CandidateEntity>)privateCandidate.Select(x => x.Id)) .WithSubquery.WhereNotExists((QueryOver<CandidateEntity>)candidatesByUserRole.Select(x => x.Id)); return QueryOver.WithSubquery.WhereNotExists((QueryOver<CandidateEntity>) excludedQueryCandidates.Select(Projections.Distinct(Projections.Id()))); } 

This is not the best solution, but should work.

Answer 2

I can not say why or against the union has such a difference. The sql profiler, query analyzer and evaluation plan should give you more information about what sql does to run your query, I believe that it does not use the right index in the first case. However, I would try to rewrite your request:

 SELECT * FROM Applicants WHERE IsActive = 1 and (this_.Id in (SELECT this_0_.Id from **FirstQueryOver** or **SecondQueryOver** or **ThirdQueryOver**)) 

and see what you have.