[TriLUG] Help with LinuxODBC to SQL Server

[matt at noway2.thruhere.net]

Good Morning TriLUG,

I have been trying to solve a problem and I am having difficulty finding a
solution, so I thought I would ask the group for some suggestions.

I have a PHP based webpage running on Slackware that connects via ODBC to
a M$ SQL-Server 2005.  I am using unixODBC and FreeTDS as the Linux driver
set and for the most part it works great.  When I have a query that
returns a large result set, I am finding that it becomes painfully slow to
retrieve that data from the SQL Server.   The PHP interface uses a fetch
row function to retrieve one row at a time from the SQL Server and loops
until there are no more rows.  Using the SQL Server profiler shows that
each of these rows calls a stored procedure function called SP_SETCURSOR
AND SP_CLOSECURSOR, or at least something to that effect.  I presume that
these are the indexes in the result set and they are getting adjusted and
I am also guessing that this is the bottleneck.  From what I can tell from
the profiler, the query result set is returned quickly and it is the
retrieval that becomes slow.  It doesn't seem to matter if I make one
query for a large block of data or break it up into smaller queries.  The
more data I return from the open connection, the slower it gets.

Looking through the PHP functions, it doesn't seem to be possible to
return all of the data at once, only row-by-row.  The use of stored
procedures that return results also seems to have been broken by an M$
'upgrade', eliminating that possibility.

Does anyone have some ideas of how to work around this?