sqlsrv drivers slow in codeigniter?

I have installed the latest version of CI 2.1.3

Now after running a query, I am getting a very slow response time for something very simple such as:

function Bash(){


    $sql = “SELECT * FROM Contacts”;
$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false) {
  die( print_r( sqlsrv_errors(), true) );
}

after querying a remote database. (Sql server 2008)

When I run this same query in a simple PHP script against the same remote database. I get results instantly.

a) Has anyone else experienced this problem with the sqlsrv drivers in codeigniter?

If so, how did you solve it?

Here is my connection string:

$db['default']['hostname'] = "xxxxx,1433";
$db['default']['username'] = "xx";
$db['default']['password'] = "xxxxxx-xx";
$db['default']['database'] = "xxxxxxxxx";
$db['default']['dbdriver'] = "sqlsrv";
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = TRUE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

UPDATE:

I have found the following from running the profiler.

DATABASE: database QUERIES: 1 (Hide) 0.0659 select * from Contacts

Loading Time: Base Classes 0.0428 Controller Execution Time ( Welcome / AzureBash ) 58.2173 Total Execution Time 58.2602

It seems as though the query is executing in 0.06 secs but the controller is taking a minute to load.

No idea why this is happening.

Solution

The active records interface for the latest SQLSRV drivers are buggy.

So, download and overwrite the existing interface with these (overwrite your sqlsrv folder in the database folder in CI):

http://www.kaweb.co.uk/blog/mssql-server-2005-and-codeigniter/

Note: These have been tested with SQL Azure and works.

$query->num_rows(); does not work with these drivers, so I suggest you use count instead. Or create your own wrapper.

In addition date is now a date object type in your result set.

I hope this helps.

Solution 2

If for whatever reason you find a bug that makes this completely unusable. Revert back to the sqlsrv interface originally provided. You will find what is causing the problem is the way the original interface are executing the query, thus, create a database helper class; use $sql = $this->db->last_query(); to get the query you was about to execute and then within the database_helper class execute it yourself:

function MakeDbCall ($sql)
{
$serverName = "xxxxx-xxxx-xxx,1433"; //serverName\instanceName
$connectionInfo = array( "Database"=>"xxx", "UID"=>"xx", "PWD"=>"xxxxx","ConnectionPooling" => "1");



 $conn = sqlsrv_connect($serverName,$connectionInfo);
 $stmt = sqlsrv_query($conn, $sql);

 while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
      $result_array[] = $row;
}

return $result_array;

}

Create one for row_array.

You should be able to call this function directly, from anywhere in your app. Whilst taking advantage of the way active_records constructs your query.

Not an ideal solution, but until codeigniter sort their SQLSRV class, there is not a lot we can do.

Solution