| 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:
|
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
]