Performance with quite some Transient

  • Resolved Benjamin Birkenhake

    (@benjaminbirkenhake)


    2 months ago

    Heya, at first: Thanks for building this plugin. I wanted to do this myself for years now, and I was very happy to see that someone did it.

    Then: I noticed that the plugin seems to have performance issues with sites with lots of transients. I tested it on two sites with 14.000+ and 7000+ transients each, and the all()-function of the Transients-class has execution time of around 150 seconds (for one call of the function). I removed the OR-Parts in the Query for _site_transient_-Transients and the plugin works nicely with those amounts of transients.

    I thought you might want to know that. I’m not an expert on sql-query performance, so I can’t offer much help here, except for the initial insight.

Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author Ariel

    (@arielhr1987)

    2 months ago

    Hi @benjaminbirkenhake Thanks for the report! I appreciate you testing it on sites with that many transients and sharing your findings.

    It does sound like the query could use some optimization, I’ll take a closer look and review the _site_transient_ part you mentioned. I haven’t had the chance to test it on installations with that amount of transients, so this feedback is very helpful.

    Thread Starter Benjamin Birkenhake

    (@benjaminbirkenhake)

    2 months ago

    I might be able to have a colleague of mine look on the queries, who is much better at optimizing SQL-Queries. But right now a lot of people are sick here (winter be damned), so it might take a while.

    Plugin Author Ariel

    (@arielhr1987)

    2 months ago

    Thanks for your help on this.

    I think the root cause of the issue is the OR in the JOIN clause, which, as you pointed out, appears to be very inefficient. At first glance, I would replace it with two joins and use COALESCE, or possibly a UNION ALL. I’ll run some tests to find the best approach and update the code accordingly.

    If any of your colleagues could also take a look, that would be great. You can find the code here:
    https://github.com/arielhr1987/leira-transients

    Plugin Author Ariel

    (@arielhr1987)

    1 month, 3 weeks ago

    @benjaminbirkenhake

    I’ve just released version 1.0.5, which includes several improvements and a fix for the query you reported in this thread.

    Could you please update to the latest version and let me know if the issue still persists? Thanks in advance for checking!

    Thread Starter Benjamin Birkenhake

    (@benjaminbirkenhake)

    1 month, 3 weeks ago

    Sorry for the late answer! I also didn’t have a chance to get a colleague to look at the plugin. It’s February and the commin cold calls collects it’s tribute. But … I just installed the Update on the two Setups I have nearby with 9.000 and 14.000 transients each and the first page loads quick like a … summerbreeze. So … well done! Thanks a lot for the effort!

    Plugin Author Ariel

    (@arielhr1987)

    1 month, 3 weeks ago

    Thanks a lot for testing the update on those setups, that’s a huge number of transients! I’m really glad to hear everything is loading fast now.

    Appreciate you taking the time to report back!

Viewing 6 replies - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.