MJOIN Server-Side Performance Hit (PHP)

MJOIN Server-Side Performance Hit (PHP)

rw152rw152 Posts: 56Questions: 15Answers: 1
edited January 2018 in Bug reports

PHP Editor Version 1.6.4

We've encountered an issue with mjoin's causing a massive slowdown on queries (anywhere from 9 - 15 seconds). Initially the work around was to use the ini_set('memory_limit','512M'); trick. While that allowed the function to execute (thus circumventing the "Allowed Memory Size..." error), it takes a very long time to complete. We are using PHP Server Side.

We have a direct link (only a single link) MJOIN on the primary table. This MJOIN links to a table with 378,530 rows of data. The parent table has 121,740 rows of data.

With the MJOIN, this particular query takes 8 seconds. Without the MJOIN, the query takes 549.68ms.

Is this an issue with the library? Is there a way to efficiently speed this up?

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Have you enabled server-side processing - like in this example. That should significantly improve performance since it will only process the records for the current page.

    In general, yes, Mjoin is far more expensive in terms of computation since it has to do a one to many link for every row.

    Allan

  • rw152rw152 Posts: 56Questions: 15Answers: 1

    Thanks, Allan. We have enabled server side processing. Our response includes draw and recordsFiltered and recordsTotal so I presume we've correctly enabled it as well?

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Assuming it is only returning 10 rows at a time (or whatever your page length is), then yes.

    Are you able to enable the debug mode for the libraries (add ->debug(true) before the ->process(...) call) and then show me the response from the server please?

    Allan

  • rw152rw152 Posts: 56Questions: 15Answers: 1

    Sure, I will PM you!

  • rw152rw152 Posts: 56Questions: 15Answers: 1
    edited January 2018

    As a note: I executed the raw sql query and it returned results in less than 1 second. The data from DT takes ~ 12

    Time (s)
    DT With MJoin: ~ 12
    DT No MJoin: < 1
    Raw SQL from DT With MJoin: < 1
  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Thanks! The issue is that the Mjoin isn't doing any limiting on its own query to get the linked data. Really what it should do is a WHERE ... IN ... based on the data that was selected from the main table.

    That probably won't make it into 1.7.1 (which will be out later this week), but it is something I will try prototyping and see what effect it has.

    Allan

  • rw152rw152 Posts: 56Questions: 15Answers: 1

    Awesome! This would give me a huge boost in performance!

  • rw152rw152 Posts: 56Questions: 15Answers: 1

    Hi Alan,

    Any chance you addressed this in 1.7.2 or 1.7.3?

    Thanks!

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin
    Answer ✓

    Currently slated for 1.8 I'm afraid. It was a non-trivial amount of work to include in a patch release.

    Allan

  • rw152rw152 Posts: 56Questions: 15Answers: 1

    I imagine! FWIW, it will dramatically improve the performance on our site. It will probably drop the load time on some pages from 30+ seconds to 1-2 seconds. It is much appreciated, thank you!! :)

  • rw152rw152 Posts: 56Questions: 15Answers: 1

    Hi Alan,

    Sorry to bother you again with this - I was wondering if you had a timeline on when you think this fix will be in place? It's resulting in 1 minute+ load times for some of our tables now and we are getting a bit desperate!

    Thank you!

This discussion has been closed.