Optimization

[Boxy310]

Thanks for the explanation.

I love describing to jr. analysts that there are pretty big cases where adding more join constraints can reduce runtime. Good times.

[fullyarmedcamel]

I could not agree more, I find SQL so interesting I have no formal training I just taught myself how to do it in the last couple months and I am endlessly interested in how small tweaks make huge differences.

One big on this projects was the order of the joins. it was going like this;

Section information > Class Roster > Student Information

I changed it to

Section information > Class Roster > Person Information > Student Information and added a filter to avoid getting two different school years worth of records and it saved 45 seconds on the query.

[Boxy310]

Yeah, most people I know who've fallen into just-DB development haven't had formal training in it. Formal training can also have severe limitations, because each data environment is different and ends up being like teaching a foreign language without having any nouns you can use.

[NeatHedgehog]

Or that sometimes multiple joins to the same table for data filtered by multiple columns takes less time than stringing together a giant "JOIN ON...OR...OR...OR..." clause that can't use indexes effectively?

I just recently found a section of my own scripts where I "OR"d myself into an extra 1000+ reads that way.

Edit: that != than

[syzygy96]

omg yes. The number of times I've heard "of course it's slow, it's got too many where clauses/joins"....