Fastest way to get counts (need to redo schema?)
So I have a dashboard page, where the general details for each course are displayed on their card. The api route I had to write for this is taking over a second every time to run (sometimes like 3 seconds). I have to make queries that calculate the stuff I want to show because they are not ready on any given table. How do I fix this? Do I have to remake my schemas and try to keep this info ready instantly on the course table? (You can see I have to get the courses someone is enrolled in, and then loop over those to do the calculation for each - very slow).
Thank you!
58 Replies
This is basically an N+1 problem
ideally you want to avoid querying inside a for loop
Yep thats what I figured was the main issue - not sure exactly how this is able to be solved, I think I gotta store what I want on the table itself
Which I think is possible (like storing count as a column)? I am rusty on my sql
Or maybe this data is not good for showing on the dashboard (where all courses are shown) and more appropriate on the specific page (so no loops but same queries done once). Just tested - doing it for one course is plenty quick. But seems like I should not be FORCED to do this.
one simple approach would be to fetch all courses + all enrolled and then match them in memory.
if you want to fix an N+1 query, ORMs provide you with some tools like include
I was considering that actually - did not try it out but basically we are doing loops again but since it is in memory it should be fairly quicker?
yeah because what slows your load is the numerous queries to the database
So now I have all the data in my memory for each course, but I would still have to do the loops and manually find the owner id and counts. Just to be clear, this is what you mean right?
also, is there a particular reason why you are fetching the enrolled courses that way?
well mmmm
yeah
If you take a look at my schema, thats the only way I thought I could 😆
can you nest that so you get the users themselves?
2 = owner, 1 = TA/UCA and 0 = student for role*
I mean
you have this, right?
Course --< Enrolled >-- User ?
Yes - User table is 3rd party auth solution but yes they are connected
To find every course a user is enrolled in, I have to look at enrolled rows that include their user id
yeah I just mean, it looks kinda weird because you are accessing the course through the include
is there join in prisma?
That is kinda what include does for you
it takes the foreign key and replaces with the actual join
oh you just include yeah ok
yeah so I have access to the course they are enrolled in
name, code, id
theoretically, a course could have like 1000 people in it, but I guess that would still be quicker than the nested queries
if you are in 10 courses, each with 1000 people, the query will have to do 10,000 iterations (i guess thats not much) (in memory way)
yeah one quick solution
would be to include the instructor_id as a reference to the user_id, like a column on the course haha.
the bad part about this approach is that you would be making future changes harder
i mean, it's not like SQL engines read every row thanks to indexing but yeah I see your point
Yeah I was also considering adding a owner_id column to each course upon creation so I dont have to look through enrolled to find the one user with role 2
Probably a good idea honestly
I would still try to get a nice one query
since you are just aggregating, it is indeed possible as a single query
i mean, it shouldn't be that confusing. (i'm not saying it would be impossible if you werent aggregating)
you are indeed just getting the creator right?
what fields do you use of the creator?
I find the user with role 2 for the course id I care about. Then take the userid of that person (the owner) and get their user details with clerk (3rd party)
I just need the user_id of the owner basically
oh ok
yeah, I HAVE to make one extra fetch for the user details but thats unavoidable
yeah
well, does clerk support getting info for an array of user_ids?
hmm, I bet they do but I am not sure what I would do with that?
because in theory you want 1 single query that returns something with the columns:
for every course the current user is enrolled in right?
you would get all the creator_ids from that result and fetch all their data in a single call
yep exactly
Ahhh I see
Thats a very very smart idea
ok first step - get that query working 😅
now that you have limited the fields you want, you can write more efficient queries using Prisma's select thingy
This is ideally what I will return (as array of these)
Yep, I will start to try and make this complex thing lol
are you using SQL behind?
(does prisma even support mongo or nosqls?)
SQL behind yea
they do support mongo but not using it
ok
i might be able to sketch up a raw SQL query in a couple hours after I get back home
that would be awesome - if I do get it working I will let u know!
cool
I think I am already close
also I found this
Prisma
Aggregation, grouping, and summarizing (Concepts)
Use Prisma Client to aggregate, group by, count, and select distinct.
Yeah that is what I was doing here
But it is its own query
Oh wait I see
yeah but you probably wanna switch to using
course.findMany(
That might be the trick
yeah because you want the aggregated column for each course
a raw query might be easier for this ngl I dont think prisma likes this much complexity lol
which prisma allows*
haha
could you paste your prisma model here?
for course and enrolled
here yah go
This could also help (postgres)
thanks, I actually translated it with chatgpt hahah
gotta go out now, but I managed to write this
it's just missing the creator_id
g2g
Oh sweeet!!
Thank u
😉
did you use it?
@Perfect
Not yet @caxco93 but I did get just member count working with prisma and went through some hell to get a prisma migration working for adding the owner id to course table
so now this ^ is very quick
coolio
I will revisit ur solution soon and add the member/instructor
because that looks like it will work, and now that I have the owner id I just have to add an easy select
yeah owner id is probably best since you want to enforce 1
i did finish the query
SELECT
c.id,
c.name,
c.code,
COUNT(CASE WHEN e.role = 0 THEN e.user_id END) AS enrolled_count,
COUNT(CASE WHEN e.role = 1 OR e.role= 2 THEN e.user_id END) AS instructor_count,
STRING_AGG(CASE WHEN e.role = 2 THEN e.user_id END,', ') AS creator_id
FROM "Course" c
INNER JOIN "Enrolled" e ON e.course_id = c.id
GROUP BY (c.id)
the problem is it is using string_agg which would in theory join various matches for role=2
so in theory you could get a USERID1, USERID2 in that column if there were 2 users with role=2
ah, there will only be 1 per course so that would have worked
sometimes raw sql is the way 🔥
well i guess it is more expressive
probably this + the owner_id column is the way to go
yep I was thinking the same thing!