CODEWITHSUNDEEP
phpsqlsql-server
Tinydan
Tinydan

Reputation: 935

MSSQL slow with PHP Query

I have a PHP page that's slow whenever it goes to query a MSSQL database. I'm using the latest PHP and the standard SQL odbc drivers to the connect to the database. When accessing the page My sql profiler goes crazy.The query is displayed then 

exec sp_cursorfetch 180150003,2,1,1
go

performs about 240 times before finally 

exec sp_cursorclose 180150003
go

occurs and the page is displayed.

Trace log Screenshot

This is the query causing the headache:

    <?php
    $con = odbc_connect('Hesk','Trace_user','*******');
    $Assets = odbc_exec($con, "SELECT  AssetName,           AssetID From viewAssets ORDER BY [AssetName];");
?>
<table Border ="0"   width="100%">
<tr>
<td style="text-align:right" width="150"><?php echo $hesklang   ['asset']; ?>: <font class="important"></font>
</td>
<td width = "80%"><select name ="asset">
<option value=""></option>
<?php
    while ($row = odbc_fetch_array($Assets))
    {
    echo '<option value="' . $row['AssetID'] . '"' .            (($_SESSION['c_asset'] == $row['AssetID']) ? '              selected="selected"' : '')
    . '>' . $row['AssetName']. '</option>';
    }
    odbc_close($con);
?>
</select></td>
</tr>
</table>

Any idea how my code is causing this performance holdup?

Upvotes: 0

Views: 2197

Answers (2)

manuel
manuel

Reputation: 1840

Try to measure time where is problem in connection or in query execution If problem is in connection then change MSSQL name to IP

$con = odbc_connect('Hesk','Trace_user','*******');

For some reason my linux box was slow on resolving.

$con = odbc_connect('192.168.10.10','Trace_user','*******');

Upvotes: 0

ChrisLively
ChrisLively

Reputation: 88072

It looks like the odbc_exec functions use cursors under the hood. I'm not entirely sure who thought that was a good idea, but it's not. Cursors are notoriously bad performers and generally a really bad idea unless you simply have no other way.

One option is to change your odbc_connect call to have the SQL_CUR_USE_ODBC flag. A better option is to use the native drivers.

http://cct.me.ntut.edu.tw/ccteducation/chchting/aiahtm/computer/phphelp/function.odbc-connect.php.htm

Upvotes: 1

Related Questions