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

Phalcon speed query

Good day!

I test phalcon in my project. I thought, it's very faster then others. But...

Look at my test

    $app = new Micro($di);
    $app->get('/', function () use ($app) {
        $users = $app->modelsManager->executeQuery("SELECT * FROM TEST WHERE NAME IN ('L488', '1K5T', '4QYS' , '9ECV')");
        $data = array();
        foreach ($users as $user) {
            $data[] = array(
                'name' => $user
            );
        }
        echo json_encode($data, JSON_UNESCAPED_UNICODE);
    });

And my model

    use Phalcon\Mvc\Model;

    class TEST extends Model {

        public $name;

        public function initialize() {
            $this->setSource("TEST");
        }

    }

The query execution time is 1.2-1.5 s. Now without phalcon

    $user = 'name';
    $pass = 'pass';

    $dbh = new PDO('oci:dbname=orcl', $user, $pass);
    $users = $dbh->query("SELECT * FROM TEST WHERE NAME IN ('L488', '1K5T', '4QYS' , '9ECV')");

    $data = array();
        foreach ($users as $user) {
            $data[] = array(
                'name' => $user
            );
        }

    echo json_encode($data, JSON_UNESCAPED_UNICODE);

The query execution time is 600-700 ms WTF? How it can be? Why phalcon modelsManager slower

1


145.0k
edited May '16

Beacause here:

$users = $app->modelsManager->executeQuery("SELECT * FROM TEST WHERE NAME IN ('L488', '1K5T', '4QYS' , '9ECV')");

You have full objects when you iterating over them, it means it needs to be hydrated, created etc, it costs time. If you need to be as fast as PDO just use:

$data = $users->toArray();

Or select columns which you need(but you will still get Row object if you will iterate over them)



2.8k
edited Jun '16

You mean change query like

     $users = $dbh->query("SELECT **NAME** FROM TEST WHERE NAME IN ('L488', '1K5T', '4QYS' , '9ECV')");

But it did not help :(

p.s. changed

     $users = $dbh->query("SELECT NAME FROM TEST WHERE NAME IN ('L488', '1K5T', '4QYS' , '9ECV')");
     $data = $users->toArray();

TIme - 1.2 s



2.8k

What i'm doing wrong?

Phalcon test

use Phalcon\Loader;
use Phalcon\Mvc\Micro;
use Phalcon\Di\FactoryDefault;
use Phalcon\Db\Adapter\Pdo\Oracle as PdoOracle;
$loader = new Loader();
$loader->registerDirs(
array(
    __DIR__ . '/models/'
)
)->register();
$di = new FactoryDefault();
$di->set('db', function () {
return new PdoOracle(
    array(
        "username" => "name",
        "password" => "pass",
        "dbname"   => "orcl"
    )
);
});
$app = new Micro($di);
$app->get('/', function () use ($app) {
$builder = $this->modelsManager->createBuilder();
$users = $builder->columns("NAME")->from("TEST")->where("NAME IN ('L488', '1K5T', '4QYS' , '9ECV')")->getQuery()->execute();
});
$app->handle();

siege -d1 -r10 -c25 https://phalcon.test

    [email protected]:/$ siege -d1 -r10 -c25 https://phalcon.test
    ** SIEGE 3.0.8
    ** Preparing 25 concurrent users for battle.
    The server is now under siege..      done.
    Transactions:                250 hits
    Availability:             100.00 %
    Elapsed time:             151.55 secs
    Data transferred:           0.00 MB
    Response time:             14.01 secs
    Transaction rate:           1.65 trans/sec
    Throughput:             0.00 MB/sec
    Concurrency:               23.11
    Successful transactions:         250
    Failed transactions:               0
    Longest transaction:           15.52
    Shortest transaction:           2.54

PDO test

$db = new PDO('oci:dbname=orcl', 'name', 'pass');
$stm = $db->query("SELECT NAME FROM TEST WHERE NAME IN ('L488', '1K5T', '4QYS' , '9ECV')");
$rows = $stm->fetchAll(PDO::FETCH_ASSOC);

siege -d1 -r10 -c25 https://pdo.test

[email protected]:/$ siege -d1 -r10 -c25 https://pdo.test
** SIEGE 3.0.8
** Preparing 25 concurrent users for battle.
The server is now under siege..      done.

Transactions:                250 hits
Availability:             100.00 %
Elapsed time:              76.31 secs
Data transferred:           0.00 MB
Response time:              6.83 secs
Transaction rate:           3.28 trans/sec
Throughput:             0.00 MB/sec
Concurrency:               22.39
Successful transactions:         250
Failed transactions:               0
Longest transaction:            7.97
Shortest transaction:           1.14

Phalcon faster? Really? WTF? Maybe slower 2x then nativ pdo



79.0k
edited May '16

What you're testing here with Micro app is just database, not Phalcon. Your query kills RDBMS, that's not optimizied at all. Any overhead you might get is by using Phalcon's ORM, so yeah - ORM will always add overhead if you compare with plain PDO.



145.0k
edited May '16

You bugged forum xD Anyways, any ORM will add overhead. I don't understand why you trying to compare ORM to PDO. Phalcon is using PDO, so how it can be faster ?

Compare it to another ORM like doctrine/propel or eloquent - then we can start talking.

ALSO what is IMPORTANT, you are not comparing QUERY TIMES. You are testing here full phalcon micro app compared to PDO, it's obvious that will be slower, beacause it gives you like 100x more ability than just pdo, routing, dispatcher, etc etc etc. If you want to compare just pdo vs phalcon orm then:

change your code:

use Phalcon\Loader;
use Phalcon\Mvc\Micro;
use Phalcon\Di\FactoryDefault;
use Phalcon\Db\Adapter\Pdo\Oracle as PdoOracle;
$loader = new Loader();
$loader->registerDirs(
array(
    __DIR__ . '/models/'
)
)->register();
$di = new FactoryDefault();
$di->set('db', function () {
return new PdoOracle(
    array(
        "username" => "name",
        "password" => "pass",
        "dbname"   => "orcl"
    )
);
});
$app = new Micro($di);
$app->get('/', function () use ($app) {
$startTime = microtime();
$builder = $this->modelsManager->createBuilder();
$users = $builder->columns("NAME")->from("TEST")->where("NAME IN ('L488', '1K5T', '4QYS' , '9ECV')")->getQuery()->execute();
echo microtime() - $startTime;
});
$app->handle();
$startTime=microtime();
$db = new PDO('oci:dbname=orcl', 'name', 'pass');
$stm = $db->query("SELECT NAME FROM TEST WHERE NAME IN ('L488', '1K5T', '4QYS' , '9ECV')");
$rows = $stm->fetchAll(PDO::FETCH_ASSOC);
echo microtime() - $startTime;

And then post body results.

It's REALLY obvious that FULL STACK FRAMEWORK will be slower than just PDO query, rotfl. There are many things happening internally in phalcon except of the code you written(like in all frameworks).

Phalcon IS NOT orm, or some database manager or whatever, it's FULL STACK FRAMEWORK, i don't understand why you compare response times of FULL STACK FRAMEWORK to just query in PDO.

It's like comparing speed of car to airplane.....



2.8k
edited May '16

I see. Thank you! I thought that phalcon using pdo without any layer. By the way, i like it. It is very cool framework which i'll use in my project



145.0k
edited May '16

But it's not only about layer :) Even if it was supposedly to work without any layer it would be still slower in this comparsion. Beacause you compared PHALCON times to PDO Query time. Not Phalcon query time to PDO query time. Check the test cases i posted, just results from body - this is your real time of queries in phalcon and pdo.

And yes phalcon introduces PHQL - higher language then SQL which is for selecting within models etc.



2.8k

I need only REST for angular. All logic in Oracle (pipeline-function, procedure). And i want to use fast rest in backend.

p.s. query time is very different

PDO Phalcon
0.021197 0.291871
0.648263 -0.735973
-0.336738 0.247738
-0.354387 0.22346
0.613935 0.268333
-0.375572 -0.701889
0.661041 0.311196
-0.320972 0.303567
-0.28953 -0.694218
-0.332529 0.213582
0.594656 0.193764


79.0k
edited May '16

For your use case, Phalcon is a perfect candidate (I'm developing mainly RESTful API's for various purposes). What I'd suggest you to use native MySQL/Oracle classes in Phalcon, i.e. to avoid any queryBuilder / ORM stuff.

Take a look for MySQL: https://docs.phalcon.io/en/latest/api/Phalcon_Db_Adapter_Pdo_Mysql.html



2.8k
in reply to Jonathan Aaron Steel
edited May '16

For your use case, Phalcon is a perfect candidate (I'm developing mainly RESTful API's for various purposes).

Using with native PDO? Like

 $connection = $this->di->getShared('db');
 $sql = "SELECT * FROM TEST WHERE NAME IN ('L488', '1K5T', '4QYS' , '9ECV')";
 $result = $connection->query($sql)->fetchAll();

What I'd suggest you to use native MySQL/Oracle classes in Phalcon, i.e. to avoid any queryBuilder / ORM stuff.

Ok, thanks. I think too.



79.0k
edited May '16

For instance, I have column defined as BIN (0,1) in MariaDB. With any ORM such simple thing is painful to achieve.

$num = 5;

$query = 'SELECT *, BIN(Active + 0) AS Active FROM CLIPS WHERE Active = ? ORDER BY ClipID DESC';
$findLimited = $this->db->limit($query, $num); //pay attention, using Phalcon limit method
$result = $this->db->query($findLimited, [1]);

if (!$result->numRows()) return parent::noResultsFound();

$res = $result->fetchAll(); //found rows, fetch them all


145.0k
edited May '16

I on mysql selecting rows with both db and phalcon orm from phalcon_test (database for testing) like this:

$startTime = microtime();
$connection = $di->getShared('db');
$sql = "SELECT * FROM personas WHERE tipo_documento_id IN (1,2,3,4)";
$result = $connection->query($sql)->fetchAll();
echo microtime()-$startTime.PHP_EOL;

$startTime = microtime();
$result = People::find([
   'conditions' => 'tipo_documento_id IN ({names:array})',
   'bind'=>['names'=>[1,2,3,4]]
])->toArray();
echo microtime()-$startTime.PHP_EOL;

Returns times like this:

0.016905 0.020497 0.02095 0.019426

So there is like really no overhead or like a none. I disabled result query cache for this.

I tested it without any micro/application etc, just plain model class, di, db, modelsMetadata and modelsManager, as you can see phalcon adds like none overhead to query(at least in mysql).



79.0k

@jurigag 0.01 vs 0.02 sec :)



145.0k
edited May '16

As you see in second example 0.02095 0.019426 0.02 vs 0.01, wtf phalcon orm faster than PDO ! :D

There is some nice repo:

https://github.com/eventhorizonpl/forked-php-orm-benchmark https://github.com/kenjis/php-orm-benchmark

Nice idea it would be to add phalcon in first one.

As you can see in second the phalcon is fastest possible orm.



22.7k

As stated by others, this has very little to do with Phalcon vs how the database is set up and database reaction time.

Phalcon overall query times and Phalcon itself is going to be slightly slower than raw php in most cases (may actually run more efficiently in multiple query benchmarks) which is amazing given you have an entire framework at your disposal. But that still leaves it 100 times faster than say Laravel, Zend and Symfony.