Database performance

Language Help SQL
1

Hey guys, I’m working on the backend career path until I hit the database optimization things,
okay it’s so useful and I understand the importance of optimizing the DB but sometimes I struggle understanding some concepts and when and how to use them as a junior and I see these tools need like a whole team to work on it.

so my question is as a junior what are the things that I should take care of it while making a DB.
and do ORMs handles most of the optimization issues?

2

I might be able to provide some information but please note that I am unfamiliar with the codecademy content on topic of database performance.

I will start with the last question:

do ORMs handles most of the optimization issues?

Definitely not. ORM is only responsible for creating the abstract layer that allows you to communicate with the database by classes and objects defined in your code - virtual representation of the data stored in your database. And because ORMs are usually engine agnostic (the same ORM can be used with multiple different database engines or different versions of a given database engine) they introduce a whole wide set of efficiency problems.

In many cases you will find that use of ORM is some kind of a trade-off. Your code can be written in a much more universal way, not tied up to the certain database engine and version (which is a great advantage when it comes to the maintenance or making the project more friendlier for another developers), but the efficiency is not as good as it could be.

as a junior what are the things that I should take care of it while making a DB

Donald E. Knuth in paper “Structured Programming with go to Statements” (which is in no way related to the DB optimization) wrote:

There is no doubt that the grail of efficiency leads to abuse. Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil.

And I believe this might be even more relevant when it comes to the learning process. Premature optimization should never stand in a way of creating projects. I would say that the best way to approach database optimization is to build something and monitor how your application behaves. Loading times are unreasonably high? Try to find the bottlenecks and solve the problem at hand.

And after some time you will build your own confidence and intuition. You will know how to write faster queries, you will know what config parameters might limit the resources used by the db engine, you will learn how to and when to create db indices etc.

Problem with the optimization, no matter what is the domain, is that it’s such a broad and deep topic. There will be always something to learn and you will not be able to learn everything that is valuable without creating and solving your very own problems.

And good employers don’t look for developers with the highest count of memorized facts. We look for people who are able to debug and solve problems when it’s needed.

Assuming that we talk here about SQL databases I would definitely appreciate if the junior dev would know:

  • what is the reasoning behind normal forms and how to spot the scheme that doesn’t comply with the first normal forms;
  • types of joins and when to use them;
  • how to define composite primary keys and when to use them;
  • what are indices;
  • how to use explain.

But that’s based on the work we do and on the state of job market in my area.

2 Likes
3

Hear, hear!

This all boils down to starting from the bottom up, not the other way around.

Especially while solving problems.

Great to see you again, @factoradic !

2 Likes
4

bro you’re awesome ! <3
Duly noted and bookmarked, thanks a lot