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.

  • 1
    At this point I'd just replace it.

    We have an awful ticket system still in use, which also needs years to process anything and is just very ugly and useless. We took a few months to setup an Open Source system now and it's just beautiful and fast and compatible and everything. Can't wait until we finally replace the old one and shut it down.
  • 1
    @jonii told him the same more than once but i think they got a contract with shitty-ticketsystem-company which requires them to stay for xy amount of time.
    Though technically they lied. But i'm not a lawyer.
  • 1
    Well Ms access does those if you edit queries in its editor but I hope they didn't use that lol
  • 1
    @KittyMeowstika it's not the contract, it's the ERP integration.
Add Comment