| Microsoft SQL and PHP [message #181846] |
Tue, 18 June 2013 23:32  |
rob.loy
Messages: 1
Registered: June 2013
Karma: 0
|
Junior Member |
|
|
|
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.
|
|
|
|
| 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: 0
|
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
|
|
|
|
| Re: Microsoft SQL and PHP [message #181936 is a reply to message #181932] |
Wed, 26 June 2013 01:29 |
Mike McGinn
Messages: 1
Registered: June 2013
Karma: 0
|
Junior Member |
|
|
Erwin Moller wrote:
> 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
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
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]