1

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.

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');
  • @doovers Artur's answer is demonstrating the correct use of Joomla query methods. If you require further explanation, please leave a comment for him. Otherwise, please award his answer the green tick to deem this page resolved and reward volunteers for donating their time. – mickmackusa Jul 23 '18 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.