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

Which is better / faster, RAW sql or ORM / PHQL?

Hi...

I am a beginner here. I have a question here:

  1. If I decide to use only mysql, what if I just skip ORM and use RAW SQL query ?
  2. What is advantage, disadvantage using RAW SQL ?
  3. Which one is perform better, ORM / PHQL / RAW SQL

Thanks

Robby



125.8k
Accepted
answer
edited Apr '15

With raw SQL, the code that interacts with the database will run faster as there are less abstraction layers to go through. However, since those abstraction layers are built in compiled C, rather than PHP, the actual difference in execution time is negligible. What you will be losing is the simple way the ORM allows you to run queries without hand coding each query.

I'm a big proponent of efficiency, but I haven't noticed any slowdown by using ORM/PHQL, and my development time is much, much lower because I can do stuff like this:

$Applicants = $Program->getApplicants()

Instead of

$Applicants = [];
$program_id = 3;
$query = "SELECT * FROM `applicants` WHERE program_id = $program_id";
$result = mysql_execute($mysqli_connection,$query);
while($row = mysqli_fetch_assoc($result)){
    $Applicant = new Applicant();
    $Applicant->program_id = $program_id;
    $Applicant->name = $row['name'];
    // and so on for every attribute
edited Feb '18

Bullshit anwser. Select a dataset with limit > 100 with sql and phql and tell me which is faster. By the way, the dataset is a table that has more than one table to join. RAW SQL will be faster for heavy sqll statements or multiple records. The orm ideea is that will cache the data and it will provide a fast response the second time.

In general, good developers (that arent juniors and dont say big proponent of efficiency, and know or should know very well the language of the db they use and the advantages and disavatanges of their dbs language) use a mix of raw sql and orm. Down side of raw sql for update inserts models will not update automatically, but in general very fast in doing their task. ORM will update the models , but is slow when large datasets are used. And by large im not talking about >10000, nope it could be even more than 10 , it all depends even on db arhitecture.(more joins slower it gets)

Dylan you gave a bad example of sql, SELECT (the star should never be used, sql [db] will not cache the request and its very possible that will not use a primary key or multiple keys because of ).

With raw SQL, the code that interacts with the database will run faster as there are less abstraction layers to go through. However, since those abstraction layers are built in compiled C, rather than PHP, the actual difference in execution time is negligible. What you will be losing is the simple way the ORM allows you to run queries without hand coding each query.

I'm a big proponent of efficiency, but I haven't noticed any slowdown by using ORM/PHQL, and my development time is much, much lower because I can do stuff like this:

$Applicants = $Program->getApplicants()

Instead of

$Applicants = [];
$program_id = 3;
$query = "SELECT * FROM `applicants` WHERE program_id = $program_id";
$result = mysql_execute($mysqli_connection,$query);
while($row = mysqli_fetch_assoc($result)){
   $Applicant = new Applicant();
  $Applicant->program_id = $program_id;
  $Applicant->name = $row['name'];
  // and so on for every attribute