10

Colleague asked me to look at his eCommerce search filtering system as the customer was complaining it was slow taking 5-6 seconds to find results.

Delving into the code deeper, I discovered he was querying the results, sticking them in an array and then sub querying the results looking at all the combinations.

On top of that each sub query looked at the database fields using "DESCRIBE" to then search them each time it found a pair!

The total query count for one page search was 14,512!!!

Why oh Why? One SQL query could of done all that in one go.

I look at other code bits he's done and he's very good in other areas. I just don't get how sometimes a good developer can make such a weird decision? It's almost as if he wanted to make it as complex as possible.

Comments
  • 7
    query him about it?
  • 2
    "One SQL query could of done it all in one go."

    I obviously don't have the whole picture of what the code was doing and what the requirements or expected behaviour is, and I'm far from an SQL expert, but I'd be careful about making such a statement.

    What you've described certainly sounds wrong, but you should make sure to understand what the real requirements for search bar results are before turning it into a single SQL query.
  • 0
  • 0
    @frankot that's what the DA said
  • 0
    @Demolishun Ask him to DESCRIBE his reasons.
  • 0
    @K4R71K I'm not sure what you mean.
  • 0
    Well e-commerce and SQL one-liners are usually mutually exclusive. Most e-commerce solutions suck because one thing: EAV. And unless it's a solution designed for a very specific product it will rely heavily on EAV because of the big variation and need of metada to fit every single product in the world. I have never needed to face a problem that needs EAV outside of academic purposes but I know if I do I probably will suck big time.

    I recommend you this of a real horror story with metadata-driven software

    https://red-gate.com/simple-talk/...
  • 0
    That's exactly me haha. I'm a good dev I think (back end) but me and mysql (queries)... 😅
Add Comment