In summary, when using odbc_fetch_row() with SQL Server 2012 in PHP it takes 17 seconds to loop through 525 results.
Environment:
odbc.ini file contents:
[ODBC Data Sources]
theDb = Actual SQL Server
[theDb]
Driver = /Library/ODBC/Actual SQL Server.bundle/Contents/MacOS/atsqlsrv.so
Description = Production SQL Server
Server = [some address]
Database = [some db]
ServerName = theDb
host = [some address]
PHP Test Page:
<?php
$userID="[user]";
$password="[password]";
$driverSourceString="theDb";
$dbc = odbc_connect($driverSourceString, $userID, $password);
$time_start=0;
function startTimer(){
global $time_start;
$time_start = microtime(true);
}
function printTimer(){
global $time_start;
$time_end = microtime(true);
$time = $time_end - $time_start;
echo sprintf('%f', $time);
echo " Seconds";
}
?>
<html>
<head>
<title>SQL TEST</title>
</head>
<body>
<pre>
Connection:
<?php
startTimer();
$dbc = odbc_connect($driverSourceString, $userID, $password);
printTimer();
?>
Query:
<?php
startTimer();
$query = "SELECT m.firstName + ' ' + m.lastName AS username FROM models m ORDER BY lastName ASC";
$result = odbc_exec($dbc, $query);
printTimer();
?>
Fetchrow:
<?php
startTimer();
while(odbc_fetch_row($result))
{
//do nothing
}
printTimer();
?>
</pre>
</body>
</html>
Output:
Connection:
0.000129 Seconds
Query:
0.061282 Seconds
Fetchrow:
15.795249 Seconds (WHY SO LONG??)
I also hade some serious performance issues with fetching results from a MSSQL database with php odbc. For me, the solution was to specify cursor type in the connection:
$con = odbc_connect([dsn], [user], [pwd], SQL_CUR_USE_ODBC)