Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Microsoft SQL and PHP
June 18, 2013, 11:32 pm
rate this thread
lication which uses ODBC as part of the PHP install to connect to the MSSQL
server 2008. I am running in to a performance issue where each SQL query
makes a round trip from the Web to the SQL server for each record so a reco
rdset with 150 results makes 150 roundtrips. Is there a setting for the ODB
C connection to return the entire recordset or should I "just"migrate to th
e Microsoft SQL Drivers? Any input would be a great help.
Re: Microsoft SQL and PHP
application which uses ODBC as part of the PHP install to connect to the MSSQL
server 2008. I am running in to a performance issue where each SQL query makes
a round trip from the Web to the SQL server for each record so a recordset with
150 results makes 150 roundtrips. Is there a setting for the ODBC connection to
return the entire recordset or should I "just"migrate to the Microsoft SQL
Drivers? Any input would be a great help.
I have had the same problem with MSSQL/ODBC/PHP combi.
I looked in options for odbcsqlextendedfetch (which seems to be able
increase batchsize), but I couldn't get it working.
I can think of 3 possible ways to solve the issue:
1) Use forward only cursor (which is faster, but still fetches them
2) Rebuild the app on the newer/better mssql-php drivers, which can be
quite a lot of work.
3) Pack the results together yourself at the MSSQL server
This is what I did, I don't remember the details, but it was easier than
a) Create a stored procedure in mssql that receives a string (which is
actually a query you want to execute)
b) Let the stored procedure execute the query, and loop over each row,
packing together the results in a big string.
c) Return the string to PHP
d) Unpack the string in PHP and create an array out of it, one slot for
It wasn't a great solution (actually it was UGLY) since I had to think
up seperator strings for the columns and rows, much like an old
fashioned CSV file.
You'll get something like this:
"12","roy","this is a comment"
"14","Joey","this is a comment with ""quotes inside"""
"19","Will","this is a comment with
And then unpack that big result with php.
I only handled strings (char/varchar/etc) and integers and floats and
NULL. Date(time) I put into a string.
It sucks a bit, but it was at least getting the result in very fast.
I have no idea if this solution is suitable for your situation.
If the app uses all kinds of odbc functions on the resultset, it will
fail of course, because you fetch your own version.
Maybe this helped. Not sure. :-)
"That which can be asserted without evidence, can be dismissed without
-- Christopher Hitchens
Re: Microsoft SQL and PHP
Have you tried the php sybase driver with freetds. It has worked for me in
the past with perl and MSSQL 2000. Not sure how it would work with MSSQL
2008, but it should be worth a shot.
Mike McGinn KD2CNU
Ex Uno Plurima
No electrons were harmed in sending this message, some were inconvenienced.
** Registered Linux User 377849