The ticket system blokes - episode 3

So we always had and have very awful performance with our ticket system. You can't get anything to load in under ~4s normally. Now since it has gotten worse over the last weeks i decided to set aside a few hours to closely watch our SQL server.
After i identified a culprit that was hogging the CPU almost every 2 minutes i looked at other long running queries in the server and found out where exactly the 4s come from.

6 tables from various DBs. Sure, no problem.

Left Outer Join. Sure, why not.

Querying every fucking column in every fucking table explicitly adding up to a whopping 160 columns which they need not even 10% of. We're talking about session IDs, passwords, stock count, IBANs and all that stuff to show the work done on a ticket. Absolutely not.

So i extracted the query and reduced it to the stuff we need and the execution time went from 4 seconds to almost instant.

The funny thing is that their idea of performance optimization is throwing LIMIT around everywhere to get these monstrous queries under control.

So in the next few days I'll have an appointment with their lead programmer. I'm looking forwards to it.

So out of curiosity: does anyone know an SQL builder or toolset that does shit like
SELECT X AS [t0_c0],
SELECT Y AS [t0_c1],
SELECT Z AS [t1_c0],
and so on? I'd like to know how they got to this point.

MySQL has no outer join. Why isn't this fact all over the internet? Why does EVERY FUCKING TUTORIAL list outer join as if it's just as standard as left join?

I tried to make some SP using the syntax and formatting that visual studio outputs when making a SSRS report. I thought it was nice.

It formats the code in a standard way and transforms stuff like "join" to "INNER JOIN" and "left join" to "LEFT OUTER JOIN ".

When the team reviewed the code they were like WTF?! This syntax is horrible, it can't be understood. You did this?

*Me with my red face*...

I just said. You know what? I am going to go back to the old school syntax if you prefer. I just thought it was better.

Yeah... You really should go back to old school syntax.

---

Keep in mind that the old school syntax is annoying to me... No formatting at all and basic instructions are not in larger upper case.

Anyway, I thought it was nice tbh. I still think it is. And it is definitely better to me in some way.

What bothers me most is that they want to improve their coding. They say they want to be more standard and it seems every time you want to make a change it's not a good idea because "everything is already written that way". And when you don't make a change, "you should have change it"... Well sorry I was just copying the old style.

Anyways , it's not that important. I do get their point. Sometimes.

Profile (1, 1) --- (1, 1) User

Right?

- A single user *must* have *exactly* 1 profile.
- A single profile *must* belong to *exactly* 1 user.

Makes sense?

I did this because i moved user profile image and user banner image into Profile entity

So now i can easily join tables and fetch user profile image based on username or user ID

By deeply thinking like an asshole and overengineering, i stumbled upon a confusion

If i can join tables and get ALL fields (assuming its a left or full outer join) from both entities...

What is the difference between choosing which entity to fetch on the frontend?

For example if i want to fetch users, inversely, i can fetch Profile entity, which has User entity as a nested object, and that way access users. Now i have access to each user's profile image, banner image, bio etc aside from the entire user object

If the user navigates to a profile page, inversely, i can fetch User entity which will have a Profile entity as a nested object, and that way show the remaining necessary fields that the profile page needs to show

I gave these inverse examples because if i want to fetch users, surely enough i can simply fetch from User entity, and if i want to fetch someones profile data i can fetch from Profile entity directly

So if this is the case, when am i supposed to fetch one over the other?

You tell me. For simplicity lets focus on these two examples. Consider this as an exam question:

1) user navigates to home page. Now paginated users with role X need to be shown, but also their profile image. Do you fetch from User or Profile entity? If you use joins which ones and why?

2) user navigates to their or someone elses profile page. Now profile-based data needs to be shown, but also the user's username and full name need to be shown. Do you fetch from User or Profile entity? If you use joins which ones and why?