sqlsrv司机慢codeigniter？ [英] sqlsrv drivers slow in codeigniter?
问题描述
我已安装最新版本的CI 2.1.3
现在运行查询后，我得到非常简单的响应时间非常慢，如：
function Bash（）{
$ sql =SELECT * FROM Contacts ;
$ stmt = sqlsrv_query（$ conn，$ sql）;
if（$ stmt === false）{
die（print_r（sqlsrv_errors（），true））;
}
。 （Sql server 2008）
当我在一个简单的PHP脚本中对同一个远程数据库运行同样的查询。我立即得到结果。
a）有没有人在codeigniter的sqlsrv驱动程序遇到这个问题？
$ b 这是我的连接字符串：
<$> c $ c> $ 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;
更新：
$ b
$ b
$ b
加载时间：基类0.0428
控制器执行时间（Welcome / AzureBash）58.2173
总执行时间58.2602
查询在0.06秒内执行，但控制器需要一分钟时间来加载。
不知道为什么会发生这种情况。
解决方案 b
$ b
最新的SQLSRV驱动程序的活动记录接口是错误的。
因此，下载并覆盖现有的接口在CI中的数据库文件夹中的sqlsrv文件夹）：
http://www.kaweb.co.uk/blog/mssql-server-2005-and-codeigniter/
注意：这些都已经过SQL Azure测试，并且可以正常工作。
$ query-> num_rows不使用这些驱动程序，所以我建议你改用计数。
此外，日期现在是结果集中的日期对象类型。
如果出于任何原因，您会发现一个错误，使这完全不可用。恢复回原来提供的sqlsrv接口。你会发现导致问题的是原始接口执行查询的方式，因此，创建一个数据库助手类;使用$ sql = $ this-> db-> last_query（）;以获取要执行的查询，然后在database_helper类中自行执行：
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;
}
为row_array创建一个。
您应该可以从应用程序的任何位置直接调用此函数。虽然利用active_records构造您的查询的方式。
不是一个理想的解决方案，但是直到codeigniter排序他们的SQLSRV类，没有很多我们可以做。 p>
解决方案
因此，请使用这些文件下载并覆盖现有界面（覆盖CI中数据库文件夹中的sqlsrv文件夹）：
http：/ /www.kaweb.co.uk/blog/mssql-server-2005-and-codeigniter/
注意：这些已使用SQL Azure测试，
$ query-> num_rows（）;不使用这些驱动程序，所以我建议你改用计数。
此外，日期现在是结果集中的日期对象类型。
解决方案2
如果出于任何原因，您发现了一个错误，导致此功能完全无法使用。恢复回原来提供的sqlsrv接口。你会发现导致问题的是原始接口执行查询的方式，因此，创建一个数据库助手类;使用$ sql = $ this-> db-> last_query（）;以获取要执行的查询，然后在database_helper类中自行执行：
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;
}
为row_array创建一个。
您应该可以从应用程序的任何位置直接调用此函数。虽然利用active_records构造您的查询的方式。
不是一个理想的解决方案，但是直到codeigniter排序他们的SQLSRV类，没有很多我们可以做。 p>
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
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.
这篇关于sqlsrv司机慢codeigniter？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
