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

Query Builder vs PHQL

Hey people!

Does anyone know if there is a significant performance hit when using the query builder instead of PHQL?
If I remember correctly some other ORMs used to convert the fluent builder interface into DSL-QL (or call it whatever you like) before converting to SQL this incurred some performance penalty.

I just ran a simple query ("SELECT * FROM Robot WHERE id = 1" on my dev box with ab -n1000 -c100 and it happened that over 10 iterations, there was indeed a drop of 9 requests per second on average (179 vs 170) which is a considerable 5% on my rig.

Any thoughts? Otherwise, does anyone know what the execution plan is for the ORM?

Cheers,
Angel

@Mapsi, phalcon is C coded, then i think that performance differences are almost ridiculous.

In my opinion, i would recommand you to use query builder for all common operation, and PHQL for hardest operations. because query builder keep your code more readable. That's my opinion.

I didnt run them, but you can also run a simple benchmark and compare time/memory usage. Because PHQL and query builder do the same final work, all the difference is in parsing PHQL/ building querry

drop of 9 requests per second compare to what?



1.4k

@SneakyBobito That's what I thought, and most likely this difference will most likely be minimal on a proper rig unlike my dev box. I hear you about the query builder and it's something I prefer myself, but the point I don't very much agree on is "use PHQL for hardest operations" only because I like consistency in the code. I know. I'm weird. But will most likely do it the way you suggest at the end. :)

@jeffreycahyono A drop of 9 requests per sec when using the query builder.



36.0k

My 5 cents.

Earlier I'm used Query Builder instead of PHQL... But, you know, "simple" JOIN query becomes more complicated and unstable when you write it with Query Builder. "Unstable": We had a lot of bugs when Phalcon (1.1) tried to convert Buider to raw SQL with LEFT JOIN and 2 parameters. May be we have "curves hands", huh, but the builder-converter produced 2 LEFT JOINs in SQL query when in Builder was only one "->jeftJoin()".