FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » Microsoft SQL and PHP
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
Microsoft SQL and PHP [message #181846] Tue, 18 June 2013 19:32 Go to next message
rob.loy is currently offline  rob.loy
Messages: 1
Registered: June 2013
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
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 10:00 Go to previous messageGo to next message
Erwin Moller is currently offline  Erwin Moller
Messages: 228
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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] Tue, 25 June 2013 21:29 Go to previous message
Mike McGinn is currently offline  Mike McGinn
Messages: 1
Registered: June 2013
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
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
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: PHP form generator
Next Topic: strange one
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Oct 23 20:39:24 EDT 2017

Total time taken to generate the page: 0.00638 seconds