Very slow query using JDatabase vs Manual

Question

I have a simple query that is taking almost a minute using JDatabase but takes less than a second when I use sqlsrv_connect.

    $time_pre = microtime(true);

    $conn_array = array (
        "Database" => "Test",
    );
    $conn = sqlsrv_connect('SQL01\SQL01', $conn_array);
    if ($conn) {
        $result = array();
        if (($qryresult = sqlsrv_query($conn,"SELECT e.*, c.Description AS Cell FROM Edms AS e LEFT JOIN Cells AS c ON c.ID=e.Cell_ID")) !== false) {
            while( $row = sqlsrv_fetch_array($qryresult, SQLSRV_FETCH_ASSOC) ) {
                $result[] = $row;
            }
        }
    }
    else {
        die(print_r(sqlsrv_errors(), true));
    }

    $time_post = microtime(true);
    $exec_time = $time_post - $time_pre;
    echo 'Time manual: '.$exec_time.'<br>';
    $time_pre = microtime(true);

    $db = $this->getDbo();
    $query = $db->getQuery(true);

    $query->select('e.*, c.Description AS Cell FROM Edms AS e LEFT JOIN Cells AS c ON c.ID=e.Cell_ID');

    $db->setQuery($query);
    $result = $db->loadRowList();

    $time_post = microtime(true);
    $exec_time = $time_post - $time_pre;
    echo 'Time Joomla: '.$exec_time.'<br>';
    $time_pre = microtime(true);

    JFactory::getApplication()->close();

output is:

Time manual: 0.046799182891846
Time Joomla: 51.681474924088

Any ideas what's going on here?

I've narrowed it down to the fetchArray() function that JDatabase calls which calls sqlsrv_fetch_array and each row of the result is taking up to 2 seconds! It seems to be doing essentially the same thing as my manual version but I've no idea why the massive difference in timing.

I've also tried $db->loadObjectList() and $db->loadAssocList() but get the same result.

Answer 1

First of all you build your query the wrong way. It should look like this:

$query = $db->getQuery(true);
$query->select('e.*, c.Description AS Cell');
$query->from('Edms AS e');
$query->leftJoin('Cells AS c ON c.ID=e.Cell_ID');