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
caxco93
caxco93•2y ago
This is basically an N+1 problem ideally you want to avoid querying inside a for loop
Perfect
Perfect•2y ago
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.
caxco93
caxco93•2y ago
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
Perfect
Perfect•2y ago
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?
caxco93
caxco93•2y ago
yeah because what slows your load is the numerous queries to the database
Perfect
Perfect•2y ago
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?
caxco93
caxco93•2y ago
also, is there a particular reason why you are fetching the enrolled courses that way? well mmmm yeah
Perfect
Perfect•2y ago
If you take a look at my schema, thats the only way I thought I could 😆
caxco93
caxco93•2y ago
can you nest that so you get the users themselves?
Perfect
Perfect•2y ago
2 = owner, 1 = TA/UCA and 0 = student for role*
caxco93
caxco93•2y ago
I mean you have this, right? Course --< Enrolled >-- User ?
Perfect
Perfect•2y ago
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
caxco93
caxco93•2y ago
yeah I just mean, it looks kinda weird because you are accessing the course through the include is there join in prisma?
Perfect
Perfect•2y ago
That is kinda what include does for you it takes the foreign key and replaces with the actual join
caxco93
caxco93•2y ago
oh you just include yeah ok
Perfect
Perfect•2y ago
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)
caxco93
caxco93•2y ago
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
Perfect
Perfect•2y ago
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
caxco93
caxco93•2y ago
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?
Perfect
Perfect•2y ago
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
caxco93
caxco93•2y ago
oh ok
Perfect
Perfect•2y ago
yeah, I HAVE to make one extra fetch for the user details but thats unavoidable
caxco93
caxco93•2y ago
yeah well, does clerk support getting info for an array of user_ids?
Perfect
Perfect•2y ago
hmm, I bet they do but I am not sure what I would do with that?
caxco93
caxco93•2y ago
because in theory you want 1 single query that returns something with the columns:
course_id, course_name, course_code, creator_id, instructor_count, student_count
course_id, course_name, course_code, creator_id, instructor_count, student_count
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
Perfect
Perfect•2y ago
yep exactly Ahhh I see Thats a very very smart idea ok first step - get that query working 😅
caxco93
caxco93•2y ago
now that you have limited the fields you want, you can write more efficient queries using Prisma's select thingy
Perfect
Perfect•2y ago
type CourseGeneral = {
id: number;
name: string;
code: string;
role: 0 | 1 | 2;
owner_name: string;
instructor_count: number;
student_count: number;
};
type CourseGeneral = {
id: number;
name: string;
code: string;
role: 0 | 1 | 2;
owner_name: string;
instructor_count: number;
student_count: number;
};
This is ideally what I will return (as array of these) Yep, I will start to try and make this complex thing lol
caxco93
caxco93•2y ago
are you using SQL behind? (does prisma even support mongo or nosqls?)
Perfect
Perfect•2y ago
SQL behind yea they do support mongo but not using it
caxco93
caxco93•2y ago
ok i might be able to sketch up a raw SQL query in a couple hours after I get back home
Perfect
Perfect•2y ago
that would be awesome - if I do get it working I will let u know!
caxco93
caxco93•2y ago
cool
Perfect
Perfect•2y ago
I think I am already close
caxco93
caxco93•2y ago
also I found this
Perfect
Perfect•2y ago
Yeah that is what I was doing here
Perfect
Perfect•2y ago
But it is its own query
caxco93
caxco93•2y ago
await prisma.user.findMany({
select: {
_count: {
select: {
posts: { where: { title: 'Hello!' } },
},
},
},
})
await prisma.user.findMany({
select: {
_count: {
select: {
posts: { where: { title: 'Hello!' } },
},
},
},
})
Perfect
Perfect•2y ago
Oh wait I see
caxco93
caxco93•2y ago
yeah but you probably wanna switch to using course.findMany(
Perfect
Perfect•2y ago
That might be the trick
caxco93
caxco93•2y ago
yeah because you want the aggregated column for each course
Perfect
Perfect•2y ago
a raw query might be easier for this ngl I dont think prisma likes this much complexity lol which prisma allows*
caxco93
caxco93•2y ago
haha could you paste your prisma model here? for course and enrolled
Perfect
Perfect•2y ago
model Course {
id Int @id @default(autoincrement())
name String @db.VarChar(255)
code String @unique @db.Char(7) // random 7 character alphanumeric uppercase string

Enrolled Enrolled[]
Meeting Meeting[]
}

model Enrolled {
user_id String
course_id Int
role Int @db.SmallInt

course Course @relation(fields: [course_id], references: [id], onDelete: Cascade)

@@id([user_id, course_id])
}
model Course {
id Int @id @default(autoincrement())
name String @db.VarChar(255)
code String @unique @db.Char(7) // random 7 character alphanumeric uppercase string

Enrolled Enrolled[]
Meeting Meeting[]
}

model Enrolled {
user_id String
course_id Int
role Int @db.SmallInt

course Course @relation(fields: [course_id], references: [id], onDelete: Cascade)

@@id([user_id, course_id])
}
here yah go
-- CreateTable
CREATE TABLE "Course" (
"id" SERIAL NOT NULL,
"name" VARCHAR(255) NOT NULL,
"code" CHAR(7) NOT NULL,

CONSTRAINT "Course_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Enrolled" (
"user_id" TEXT NOT NULL,
"course_id" INTEGER NOT NULL,
"role" SMALLINT NOT NULL,

CONSTRAINT "Enrolled_pkey" PRIMARY KEY ("user_id","course_id")
);

-- CreateIndex
CREATE UNIQUE INDEX "Course_code_key" ON "Course"("code");

-- AddForeignKey
ALTER TABLE "Enrolled" ADD CONSTRAINT "Enrolled_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "Course"("id") ON DELETE CASCADE ON UPDATE CASCADE;
-- CreateTable
CREATE TABLE "Course" (
"id" SERIAL NOT NULL,
"name" VARCHAR(255) NOT NULL,
"code" CHAR(7) NOT NULL,

CONSTRAINT "Course_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Enrolled" (
"user_id" TEXT NOT NULL,
"course_id" INTEGER NOT NULL,
"role" SMALLINT NOT NULL,

CONSTRAINT "Enrolled_pkey" PRIMARY KEY ("user_id","course_id")
);

-- CreateIndex
CREATE UNIQUE INDEX "Course_code_key" ON "Course"("code");

-- AddForeignKey
ALTER TABLE "Enrolled" ADD CONSTRAINT "Enrolled_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "Course"("id") ON DELETE CASCADE ON UPDATE CASCADE;
This could also help (postgres)
caxco93
caxco93•2y ago
thanks, I actually translated it with chatgpt hahah gotta go out now, but I managed to write this
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
FROM Course c
INNER JOIN Enrolled e ON e.course_id = c.id
GROUP BY (c.id)
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
FROM Course c
INNER JOIN Enrolled e ON e.course_id = c.id
GROUP BY (c.id)
it's just missing the creator_id g2g
Perfect
Perfect•2y ago
Oh sweeet!! Thank u
caxco93
caxco93•2y ago
😉 did you use it? @Perfect
Perfect
Perfect•2y ago
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
Perfect
Perfect•2y ago
Perfect
Perfect•2y ago
so now this ^ is very quick
caxco93
caxco93•2y ago
coolio
Perfect
Perfect•2y ago
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
caxco93
caxco93•2y ago
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
Perfect
Perfect•2y ago
ah, there will only be 1 per course so that would have worked sometimes raw sql is the way 🔥
caxco93
caxco93•2y ago
well i guess it is more expressive probably this + the owner_id column is the way to go
Perfect
Perfect•2y ago
yep I was thinking the same thing!
Want results from more Discord servers?
Add your server