We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Slow PHQL queries compare to raw.

Hello! I'm choosing a new framework for a modern and fast ecommerce store. There will be a lot of sql queries, so the speed is very important. For this issue I'm using: mySQL and table with 50 000 products.

With Phalcon PHQL:

Setup from INVO

first query - 0.0076 sec:

second query - 0.0031 sec:

$product = Product::findFirst(25193);

With raw mysqli:

Much faster.

first query - 0.00074 sec:

    $mysqli = new mysqli("localhost", "root", "password", "db");
    $stmt = $mysqli->query("SELECT * FROM product WHERE product_id = 25193");
    $row = mysqli_fetch_assoc($stmt);

So with a lot of joins and many queries an app will be to slow. Or I'm doing something wrong?



79.0k

That's the price you pay for ORM and/or Active record.

P.S. You'd better try with PDO instead of MySQLi driver when you compare Phalcon ORM vs. PHP raw.



93.7k
Accepted
answer
edited Apr '17

As @Jonathan said, using the ORM in every framework will always be slower than anything else. ORM's fetch whole objects, relations and other crap. When using ORM you have to use model relations (hasMany, belongsTo etc...) which will cause n+1 problem and you said you will have a lot of queries, this will be problematic with every framework.

You may want to take a look at Phalcon Query Builder, it's elegant way to create queries, allows you to do joins which are way faster than ORM relations. https://docs.phalcon.io/en/3.0.2/api/Phalcon_Mvc_Model_Query_Builder.html.

You said "modern", so might as well use PDO, since mysqli is pretty old already :)

And never ever do select * when aiminig for performance ;)



1.1k
in reply to Jonathan Aaron Steel

Jonathan, for your opinion, can I benefit from using Phalcon ORM on ecommerce? Or it always will slow down app with every new query? Yes, I’ll be using several Memcached layers. But the main operations in ecommerce is reading large joined tables. Now for our projects we are using Opencart with cache, so server response time is 30 ms average with full cache and 45-60 ms with partial cache.

That's the price you pay for ORM and/or Active record.

P.S. You'd better try with PDO instead of MySQLi driver when you compare Phalcon ORM vs. PHP raw.



1.1k
in reply to Nikolay Mihaylov

Nikolay, yes SELECT * was for quick example and test :) We are profiling and test queries. Will try Phalcon Query Builder. Thank you for your reply!

As @Jonathan said, using the ORM in every framework will always be slower than anything else. ORM's fetch whole objects, relations and other crap. When using ORM you have to use model relations (hasMany, belongsTo etc...) which will cause n+1 problem and you said you will have a lot of queries, this will be problematic with every framework.

You may want to take a look at Phalcon Query Builder, it's elegant way to create queries, allows you to do joins which are way faster than ORM relations. https://docs.phalcon.io/en/3.0.2/api/Phalcon_Mvc_Model_Query_Builder.html.

You said "modern", so might as well use PDO, since mysqli is pretty old already :)

And never ever do select * when aiminig for performance ;)



145.0k
edited Apr '17

It's pretty obvious that raw sql will be always faster than orm. But with orm it gives you much more ability and it's more powerful.