Improving ADODB speed

Bug #1427046 reported by Robert Lyon on 2015-03-02
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
Medium
Robert Lyon

Bug Description

To make ADODB function faster I found the following information:

----------------------------------------------------------------------------------------------

High Speed ADOdb

ADOdb is a big class library, yet it consistently beats all other PHP class libraries in performance. This is because it is designed in a layered fashion, like an onion, with the fastest functions in the innermost layer. Stick to the following functions for best performance:
Innermost Layer

Connect, PConnect, NConnect
Execute, CacheExecute
SelectLimit, CacheSelectLimit
MoveNext, Close
qstr, Affected_Rows, Insert_ID

The fastest way to access the fields is by accessing the array $recordset->fields directly. Also set the global variables $ADODB_FETCH_MODE = ADODB_FETCH_NUM, and $ADODB_COUNTRECS = false before you connect to your database.

Consider using bind parameters if your database supports it, as it improves query plan reuse. Use ADOdb's performance tuning system to identify bottlenecks quickly.

Installing the ADOdb C extension will speed up GetAll() and GetArray() by 100%, and GetAssoc() by 30%. It will also speed up oci8 select statements by 30%.

Lastly make sure you have a PHP accelerator cache installed such as APC, Turck MMCache, Zend Accelerator or ionCube.

Advanced Tips

If you have the ADOdb C extension installed, you can replace your calls to $rs->MoveNext() with adodb_movenext($rs). This doubles the speed of this operation. For retrieving entire recordsets at once, use GetArray(), which uses the high speed extension function adodb_getall() internally.

Execute() is the default way to run queries. You can use the low-level functions _Execute() and _query() to reduce query overhead. Both these functions share the same parameters as Execute().

If you do not have any bind parameters or your database supports binding (without emulation), then you can call _Execute() directly. Calling this function bypasses bind emulation. Debugging is still supported in _Execute().

If you do not require debugging facilities nor emulated binding, and do not require a recordset to be returned, then you can call _query. This is great for inserts, updates and deletes. Calling this function bypasses emulated binding, debugging, and recordset handling. Either the resultid, true or false are returned by _query().

Robert Lyon (robertl-9) wrote :

The simplest way to speed up ADODB on a linux machine is to install the php5 adodb c library. Adodb automatically detects if it is present and uses it.

  sudo apt-get install php5-adodb

Robert Lyon (robertl-9) on 2015-03-02
Changed in mahara:
assignee: nobody → Robert Lyon (robertl-9)
status: New → In Progress
Mahara Bot (dev-mahara) wrote :

Patch for "master" branch: https://reviews.mahara.org/4340

Reviewed: https://reviews.mahara.org/4340
Committed: http://gitorious.org/mahara/mahara/commit/3018d05a9f329b2f97ba1ee7782e4a6dfdfb359b
Submitter: Aaron Wells (<email address hidden>)
Branch: master

commit 3018d05a9f329b2f97ba1ee7782e4a6dfdfb359b
Author: Robert Lyon <email address hidden>
Date: Tue Mar 3 11:43:26 2015 +1300

Adding info about installing the adodb c lib (Bug #1427046)

To test - read the new instructions in the README file and try
installing it on your machine and then test to make sure Mahara is not
broken.

Advanced test - install xhprof and make sure the use of this lib does
in fact make things faster.

Change-Id: Ia4aff333df7353d0aa40c771264e4c1be7d94f51
Signed-off-by: Robert Lyon <email address hidden>

Changed in mahara:
status: In Progress → Fix Committed
importance: Undecided → Medium
milestone: none → 15.04.0
Robert Lyon (robertl-9) on 2015-04-17
Changed in mahara:
status: Fix Committed → Fix Released
Mahara Bot (dev-mahara) wrote :

Reviewed: https://reviews.mahara.org/4338
Committed: https://git.nzoss.org.nz/mahara/mahara/commit/c22a898629bdbf7b8552dae01d30515b384de8d0
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit c22a898629bdbf7b8552dae01d30515b384de8d0
Author: Robert Lyon <email address hidden>
Date: Mon Mar 2 17:09:04 2015 +1300

DB execute functions without prepare values can go direct to _Execute()

Bug #1427046

to reduce query overhead

Change-Id: I4eb6d2531aee54ed1cb4d5f471b3d0032f259379
Signed-off-by: Robert Lyon <email address hidden>

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers