MJOIN Server-Side Performance Hit (PHP)

MJOIN Server-Side Performance Hit (PHP)

rw152rw152 Posts: 29Questions: 9Answers: 1
edited January 10 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?

Answers

  • allanallan Posts: 44,186Questions: 1Answers: 5,869 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: 29Questions: 9Answers: 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: 44,186Questions: 1Answers: 5,869 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: 29Questions: 9Answers: 1

    Sure, I will PM you!

  • rw152rw152 Posts: 29Questions: 9Answers: 1
    edited January 22

    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: 44,186Questions: 1Answers: 5,869 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

Sign In or Register to comment.