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.

回答1:

Adding an answer to this after the answer has already been accepted because I found a different solution. I was having the same problem ... looping through the result set was very very slow. i opened system/database/drivers/sqlsrv/sqlsrv_driver.php and found the connection function. i noticed that is was using the SQLSRV_CURSOR_STATIC option. i changed this to SQLSRV_CURSOR_CLIENT_BUFFERED and my slowness problems went away. See documentation for this here:

http://msdn.microsoft.com/en-us/library/hh487160(v=sql.105).aspx

I honestly have no idea what the sql server driver for php is doing, however, given the speed up, etc i can guess that the driver might be using a cursor by default. this seems like an awful idea. i also am assuming that by choosing client_buffered the data for the query would b e read without a cursor and accessed in memory on the client as if it were a cursor. If this is the case, bad things might happen if you try to execute a query that has many many rows to read. Perhaps another option (SQLSRV_CURSOR_FORWARD?) can be used to read data without a cursor - but i'm sure the methods used to access the query will be more limited (e.g. not using result_array())

-Don

回答2:

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.

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.

回答3:

What is your connection string? You can specify the "network protocol" explicitly, which somtimes can affect speed.

http://www.connectionstrings.com/articles/show/define-sql-server-network-protocol

"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=myUsername;Password=myPassword;"

By specifying the IP address, the port number (1433) and the Network Library, you are providing a very granular connection string. Your details may vary of course.

Alot of times, you don't need this. But I've been on a few client trips where this was the magic dust.

回答4:

You might want to turn db_debug to FALSE which should save time debugging the database.

Also, would suggest to turn cache_on to FALSE and specify cachedir and use $this->db->cache_on(); for queries that are less dynamic, i.e. does not change frequently.

回答5:

For speed up fetch up to 3 times please use "MultipleActiveResultSets"=>'0' in your sqlsrv_connect connection options.

Ex:

$db = sqlsrv_connect('127.0.0.1', array('Database'=>'dbname','UID'=> 'sa','PWD'=> 'pass',"CharacterSet" =>"UTF-8","ConnectionPooling" => "1"
                    ,"MultipleActiveResultSets"=>'0'

            ));