Very poor performance with many to many sql relations

REST API
1

Hello,

we experienced very bad performance with many to many relations when using REST GET requests with parameter “related”.
We have about 300 records in a table “trainings” and 60 records in a table “targetgroups”. They are connected with an intermediate table “trainings_targetgroups” which contains 200 records.
Getting only the trainings without related data is done in 650 ms. That’s fairly ok.
But getting the trainings with related targetgroups needs more than 8500 ms. This is not usable in production.
Database is MySQL. Getting the same data (with related targetgroups) from the same server with an own php script that generates similar JSON response is done in 880 ms (also with ORM).
We experienced this on different servers with different MySQL databases and this behaviour is reproducable for each installation.
We are using Dream Factory Version 2.1.2 with Apache running on Ubuntu 14.04 Servers.
Is there anything we could change in our configuration to get a better performance?

2

Hello again!
Does nobody else have this performance issues with many to many relations?
Thanks a lot for your feedback in advance!

Best regards

Alex

1 Like
3

I haven’t seen any such performance issues, though it’s possible it could be related to the specs of the server hosting DreamFactory.

I’m curious, are you seeing this issue in API Docs? If yes, have you tried using another rest client, such as the included test_rest (http://yourdfURL/test_rest.html) or Postman?

API Docs is a great tool for learning how to make calls but it has A LOT of overhead and is not intended to be a performance indicator.

1 Like
4

Yes, I have performance issues too Apache is dying. All posts I have seen says change MaxClient settings. However shipped httpd.conf does not allow for adding this line. Apache wont startp. Nothing wrong with my server. Windows Server 2008R2 with Quad Core 16gb RAM. My dbs are running at less than 1% DTU. One request to apache and it spikes 50% CPU??? 10 Requests and it crashes.

5

@bohndorf there was a big change to related data handling in the last release (2.3.1,) specifically this commit here: https://github.com/dreamfactorysoftware/df-sqldb/commit/b5d3e5e3a5d722b4765f6af1ca325820b75fb00d

You should see greatly improved performance on related data. In one circumstance I tested, I was able to reduce API call response time by 1/10.