It;s not easy to answer such a topic since there is not enough information about your database, DF setup, foreign keys, virtual keys etc. I advice you to enable mysql query logs and perform api request and ci request to see differences between SQL queries. For example DF may be using virtual foreign keys, not optimized/indexed fields to seek data etc. If the queries are exactly the same, look for the previous queries DF is performing and be sure that caching is not affecting the results (perform requests in between stopping/starting mysql service) etc.
@Hussain_Fakhruddin, I would make sure DF and CI both using the same caching mechanism. DF uses file based cache by default but that can be easily changed to use Redis or Memchached. As @tkorkunckaya suggested, check to see if there are any differences in queries performed by DF and CI at the mysql query log. If you can give us more information about your tables (schema, foreign key relations etc.) , API calls, benchmark data that would give us better visibility on this issue.
Also, it’s worth mentioning that we have made several performance related enhancements in the later versions of DF. You can try using DF 2.7 (latest release) and see if that helps with the performance.
A simple independent CORE PHP script with DB connection and a single query. Here is the output of the mysql logs:
Next, I wrote the same in CodeIgniter , I got :
DreamFactory’s CUSTOM script with a single query on a mysql table called “event” which has list of events. No joins on that table. Simple Select * from event ( I have connected to the DB manually by giving the credentials )
So I can clearly see that there are more DB calls in DF which could be a major performance issue.
I am getting DF is 2-5 times SLOWER than normal CodeIgniter . Of course I agree DF offers more ( Roles / Authentication , Security ), but 2-5 times performance degradation is something really concerning. In one of the blogs, its said there will be a marginal increase, but I found it otherwise.
The next set of question is:
Can the ROLES be stored in a file and loaded up once, so that the application remembers in memory rather than to query it ?
Obviously usertoken etc cannot be stored in file etc, so that’s one extra query we have to spend.
Can prepared statements be disabled in DF ?
Can Pre-scripts/Post scripts queries be disabled if we’re not using them ? OR can it be stored in a FILE ?
For custom scripts, instead of storing them in DB, can they be part of a file for faster access ( directly included ? )
Hi @Hussain_Fakhruddin, as you said, DF does a lot more than what you are trying to compare with using CI and plain PHP code. However, some areas from the DF query log could definitely use some optimization/enhancements and few of them are already on our todo list. That being said, here are answers to your questions.
01 & 02. User - app - role mapping are stored in cache. If you enable cache you should only see this once.
03. DF uses Laravel PHP framework which uses prepared statement. It’s a good practice to use prepared statement anyway.
04 & 05. This is already on our list of things to enhance.
DF may seem slower in your comparison but in reality it is doing a lot of heavy lifting such as user management, role based access control, managing scripts (pre, post, queued), routing and managing API endpoints etc. If you add all these layers of handling to an app built using CI you will be adding some overhead there as well.
But at the same time 2-5 times slower would mean a lot in the production system in terms of scalability.
Looking forward to tweaking the performance a bit more in the coming days. If my team comes up with some more optimization, I will surely share and contribute it so that you can integrate it in the core product.
@Hussain_Fakhruddin, I agree that the startup time dreamfactory adds doing authentication, apps, roles, etc would add some time before the start of the query, but that time stays pretty constant. As the complexity of the query increases, this overhead feels less painful. On the simplest query, yeah, it could be 5x slower, but on complex queries that overhead becomes much smaller relative to the time to actually execute the query. Especially if you’re using views, the dreamfactory overhead becomes negligible.
These numbers have no basis in fact; they’re just an example. Imagine you have a query that takes 20ms directly in SQL. In DF, this takes 120ms. That’d be a 6x slowdown. But on a query that takes 800ms in SQL, DF takes 900ms. Now it’s only a 1.13x slowdown. Tack on connection speed, time to parse and render the request in the browser, etc. and the overhead from DF becomes negligible. Sure, there is always some room for improvement, but I’ve found it’s more likely to be in my parsing/rendering code or query optimization than in the DF platform.
