Skills query is slooooooow and returning unnecessary information

[mkimberlin]

The skills query is pretty slow and is including terminated team members. I am not 100% sure one is causing the other, but I am 98% sure that both can be addressed with a single change. 😉

Screen.Recording.2025-02-17.at.2.15.43.PM.mov

[ocielliottc]

@mkimberlin After doing a little testing, I don't think it's going to be as easy as filtering out terminated member skills. The only association between skills and members is the skillid and memberid. The way we currently do it is we find all the skills and then we match up the member profiles that have that skill. We have to look at all of the skills in the repository and then look at all of the member profiles that have that skill and then see who is terminated. By the time we can filter, all of the time has been taken to search both repositories. I think that's where all of the time is taken.

[ocielliottc]

For my test, I added 200 skills and 200 terminated users that each had all 200 skills. The query to get the skills took 15.3 seconds and to filter out the terminated users took less than 1ms.

[ocielliottc]

This is the script I wrote to generate the .sql file for testing.
generate_skills.zip

[ocielliottc]

Could we write a custom query in MemberSkillRepository to do the filtering at the db level?

[mkimberlin]

@ocielliottc clearly you aren't busy enough on your project yet. lol.

That was my plan...to write a specific query for this.

[ocielliottc]

You are correct.

I'm about to test this one:

select member_skills.*
FROM member_skills
INNER JOIN member_profile
ON member_skills.memberid = member_profile.id
WHERE :targetId = member_skills.skillid
AND member_profile.terminationdate IS NULL