NHibernate - Union three QueryOvers

Question

Hi StackOverflow users,

I ran into this problem I have three QueryOvers and each of them returns a list of candidate ids that I then use to bring those 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 this

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 'or'. Because of this the query 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 finishes almost instantly.

Do you have any idea on how should I refactor the code for better performance?

Thank you, Adrian.

Flasket

is doing an in-memory Union an option? – Valadez

@Andrew Whitaker I ended up doing just that but I don't really like it because there are records that are returned by more than one query so I have to eliminate the duplicates manually. – Flasket

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())));
}

It isn't the best solution, but should work.

Curb

Answer 2

I can't say why the or vs the union has such a diffrence. The sql profiler, query analyzer and estimated execution plan should give you more insight in what sql does to run your query, I guess it is not using the right index in the first case. However I would try to rewrite your query to:

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

and see what preformance you have then.

Tabby

Hi peer, thanks for your help. I will check the indexes that I have on my columns. However, I would highly appreciate if you could give me hints in refactoring the c# code. – Flasket