sql server - sqlsrv drivers slow in codeigniter? -

i have installed latest version of ci 2.1.3

now after running query, getting slow response time simple such as:

function bash(){       $sql = “select * contacts”; $stmt = sqlsrv_query( $conn, $sql ); if( $stmt === false) {   die( print_r( sqlsrv_errors(), true) ); } 

after querying remote database. (sql server 2008)

when run same query in simple php script against same remote database. results instantly.

a) has else experienced problem sqlsrv drivers in codeigniter?

if so, how did solve it?

here 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 following running profiler.

database: database queries: 1 (hide) 0.0659 select * contacts

loading time: base classes 0.0428 controller execution time ( welcome / azurebash ) 58.2173 total execution time 58.2602

it seems though query executing in 0.06 secs controller taking minute load.

no idea why happening.

solution

the active records interface latest sqlsrv drivers buggy.

so, download , overwrite existing interface these (overwrite sqlsrv folder in database folder in ci):

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

note: these have been tested sql azure , works.

$query->num_rows(); not work these drivers, suggest use count instead. or create own wrapper.

in addition date date object type in result set.

i hope helps.

solution 2

if whatever reason find bug makes unusable. revert sqlsrv interface provided. find causing problem way original interface executing query, thus, create database helper class; use $sql = $this->db->last_query(); query execute , within database_helper class execute 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 1 row_array.

you should able call function directly, anywhere in app. whilst taking advantage of way active_records constructs query.

not ideal solution, until codeigniter sort sqlsrv class, there not lot can do.

adding answer after answer has been accepted because found different solution. having same problem ... looping through result set very slow. opened system/database/drivers/sqlsrv/sqlsrv_driver.php , found connection function. noticed using sqlsrv_cursor_static option. changed sqlsrv_cursor_client_buffered , slowness problems went away. see documentation here:

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

i have no idea sql server driver php doing, however, given speed up, etc can guess driver might using cursor default. seems awful idea. assuming choosing client_buffered data query b e read without cursor , accessed in memory on client if cursor. if case, bad things might happen if try execute query has many many rows read. perhaps option (sqlsrv_cursor_forward?) can used read data without cursor - i'm sure methods used access query more limited (e.g. not using result_array())

-don