N + 1 Queries: The Easiest Way To Improve Performance

Показать описание

🌎 Find Me Here:

#Shorts

Рекомендации по теме

Комментарии

For someone working many years as a db dev, this was obvious on first sight, good job sharing this with everyone

edisvelicanin

In Salesforce we do this all the time as there is governor limit of SOQL 101 which limits the developer to only 100 queries per transaction. Above that the code will stop executing and throw an exception. And, 100 queries might seem a lot but while working on enterprise level systems, even that is not enough.😅

TheShekhar

It isn't a simple SQL question nesting?

yajirushik

It's a SQL skill, not a JS one 😉

victormog

Fantastic advice. When I used to train developers, I used to use this kind of example for a philosophy I called “Velocity Thinking.” The gist is something that seems perfectly performant in staging with three dummy records will be very different in production with hundreds of thousands of concurrent users and potentially millions of records. I would say that a tiny pebble when moved at a thousand miles a minute, is no longer a pebble — it’s a bullet. Every developer should be doing this kind of optimization at every opportunity they can and it’s best of all to have that mindset from the very start.

robertholtz

Had no idea this was a problem prevalent enough to be named

robertgomez

If you're doing two queries per request, is it n + 1, or 2n?

carlonnrivers

Can you share more similar topics? I find it super useful even for engineers with many years of experience

yw

If each book has zero or one author (i.e. if you have a "many to one relation"), just doing a join is fine, but if a book can have multiple authors (i.e. if you have a "many to many" relation), this no longer works, as you'd get duplicated books if you join with a junction table (unless maybe you collapse the author data with GROUP_CONCAT and the like, but let's say you want a full parsable data, not stringified). It also doesn't work if you want to get one author and all of their books (i.e. a one to many relation).

The way to solve that (which also works pretty effeciently in a many to one relation) would be to, in the same request, get all books (filters, pagination and all) then get all authors joined with the junction table, where the book id is among the fetched books. Then merge the results (e.g. by looping over each author and putting it at the appropriate book based on the book id from the junction table).

This is sort of what ORMs do under the hood actually.

In theory, I guess you could also do joins even with a many to many relation, and loop over the result to de-duplicate it, but depending on how many tables are part of the query and how many rows the total are, the de-duplication may end up less efficient than a simple extra query plus merge.

boenrobot

How are you getting SQL syntax highlighting in a js string? Pretty awesome, also watch out with SQL injection vulnerabilities when using ${x} in string literals

creekytwenty

in case of JOIN better using keyword USING instead of ON when both keys are the same. Like USING(id)

IgorAlov

obvious but stiil tnx for your efforts !

EugenKondratiev

What about for inserts and updates that require check if exist? Most of the time those require 2N queries. Is there a way to do it in 1 or 2?

Leo_Chan

Howd you get your SQL query looking like that inside double quote? Is that from extension?

seringturu

i know the authorid value comes from a query result but concatenating strings to create an sql query is a very bad habit. You shouldn't do that, especially in an educational video.

NicolasLeclerc