| Re: Microsoft SQL and PHP [message #181932 is a reply to message #181846] |
Tue, 25 June 2013 14:00   |
Erwin Moller
Messages: 228
Registered: September 2010
Karma:
|
Senior Member |
|
|
On 6/19/2013 1:32 AM, rob(dot)loy(at)gmail(dot)com wrote:
> I am running PHP 5.2 on a Windows machine using Apache. It is a legacy 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.
>
Hi,
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
one-by-one)
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
it sounds:
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
each row.
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:
*COLNAMES*"userid","username","comment"*ENDCOLNAMES*
*DATATYPE*"integer","STRING","STRING"*ENDCOLNAMES*
"12","roy","this is a comment"
"14","Joey","this is a comment with ""quotes inside"""
"19","Will","this is a comment with
newlines.
yes"
etc.
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. :-)
Good luck.
Regards,
Erwin Moller
--
"That which can be asserted without evidence, can be dismissed without
evidence."
-- Christopher Hitchens
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]