MJOIN Server-Side Performance Hit (PHP)

rw152

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?

allan

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

rw152

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?

allan

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

rw152

Sure, I will PM you!

rw152

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

allan

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

rw152

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

rw152

Hi Alan,

Any chance you addressed this in 1.7.2 or 1.7.3?

Thanks!

allan

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

Allan

rw152

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!!

rw152

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!