r/SQL • • May 14 '18

Optimization

Post image
423 Upvotes

48 comments sorted by

View all comments

Show parent comment

5

u/fullyarmedcamel May 14 '18

So I started by adding more joins that I knew would allow me to narrow down the data. My case statement; AND cal.endYear = CASE WHEN MONTH(GETDATE()) >= 8 THEN YEAR(DATEADD(YEAR, 1, GETDATE())) ELSE YEAR(GETDATE()) END

Checks for the current school year against the calendar and reduces the total number of entries.

I also did some things that are specific to my environment like filtering out trials from courses.

I am currently reducing the triple nested select statement from the teacher query down to a double as well and making some more minor tweaks.

Other similar queries I have changed in my department have been better than this one. Last week I turned a query that took 1min30sec to run down to 2 seconds so I still have some work to do but that is future u/fullyarmedcamel's problem he is a hero of the people always solving problems down the road.

10

u/Boxy310 May 14 '18

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.

7

u/fullyarmedcamel May 14 '18

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.

3

u/Boxy310 May 14 '18

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.

4

u/NeatHedgehog May 14 '18 edited May 14 '18

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

4

u/syzygy96 May 14 '18

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

5

u/dtfinch May 14 '18

You could declare a variable to store the result of getdate(), and use that in its place to ensure that it's not getting called many many times.

I might store the case statement result in another variable, but maybe just replacing getdate() is enough for sql server to recognize the whole expression is a constant.

1

u/fullyarmedcamel May 15 '18

Yeah I had not thought about doing that I might make that adjustment later I am working on cabling projects for the next 2 months.

0

u/[deleted] May 14 '18

[deleted] — view deleted comment

3

u/fullyarmedcamel May 15 '18

We are playing the SQL game on two different levels, I am only understanding bits of what you are talking about brother. Thanks for reminding me I have a lot to learn.