Hello.
I have two collections:
Annotations
Project
We are developing an application in C# where we want to provide complex filtering capabilities.
the following query is not complex, but we already run into performance problems.
So.
The c# code:
var filterResponse = await applicationRepository.AsQueryable<Domain.Annotation.Annotation>()
.Join(
applicationRepository.AsQueryable<Project>(),
a => a.Project,
p => p.Id,
(annotation, project1) => new
{
annotation,
project1,
})
.Join(
applicationRepository.AsQueryable<Subject>(),
p => p.annotation.Subject,
s => s.Id,
(arg1, subject) => new FilterResponse
{
Annotation = arg1.annotation,
Project = arg1.project1,
Subject = subject,
})
.Where(f => f.Subject.SubjectName == "adhesion assay")
.ToListAsync();
the translated query:
"pipeline":[
{
"$project":{
"_outer":"$$ROOT",
"_id":0
}
},
{
"$lookup":{
"from":"projects",
"localField":"_outer.project",
"foreignField":"_id",
"as":"_inner"
}
},
{
"$unwind":"$_inner"
},
{
"$project":{
"annotation":"$_outer",
"project1":"$_inner",
"_id":0
}
},
{
"$project":{
"_outer":"$$ROOT",
"_id":0
}
},
{
"$lookup":{
"from":"subjects",
"localField":"_outer.annotation.subject",
"foreignField":"_id",
"as":"_inner"
}
},
{
"$unwind":"$_inner"
},
{
"$project":{
"Annotation":"$_outer.annotation",
"Project":"$_outer.project1",
"Subject":"$_inner",
"_id":0
}
},
{
"$match":{
"Subject.Subject":"adhesion assay"
}
}
],
There are 1-1 connections between the tables.
The query is very slow.
I tried to put an index on the subject field, but it doesn’t use it.
On another site I read up on why this might be:
Is this really the case ? can the mongo db not optimize this ?
how can this query be written in a more optimal way , if I want to continue to start from the annotation table?
So the Concept is to do this 3 table concatenation , and you can write filters for practically any field.
Is it possible to implement this in a way that works fast ?
Thank you in advance for your help and reply.