ODBC (mssql) fetches results really slow
- From: Erwin Moller <Since_humans_read_this_I_am_spammed_too_much@xxxxxxxxxxxxxxxx>
- Date: Fri, 17 Apr 2009 14:30:30 +0200
{This is a repost from comp.databases.ms-sqlserver)
Hi group,
In my current project I have a performance problem.
I hope somebody can help with it.
- MSSQL 2005 server
- PHP5/IIS running over ADOBD abstractionlayer. (using DNS-less odbc_mssql)
global $ADODB_COUNTRECS;
$ADODB_COUNTRECS = false;
$connection = &ADONewConnection('odbc_mssql');
// $connection->debug = true;
$dsn = "Driver={SQL Server};Server=$server;Database=$dbdatabasename;";
$connection->Connect($dsn,$dbusername,$dbpassword);
I pinpointed the bottleneck as follows:
1) A certain QueryX takes > 60 seconds to complete from my application.
2) That same queryX takes 2 seconds to complete when executed in SQL Server Management Studio.
3) By adding timestamps into my code I found out that all the time is consumed by fetching the resultset into PHP/IIS.
For reasons beyound my control I cannot switch to the native driver.
(I did some tests with it, and the result came in bloody fast when using native drivers, but other parts of the application give trouble when using it, and there is no money for a complete overhaul.)
I am no ODBC expert, but I tried to catch up a little by reading on the web. I have the impression my slow performance is caused by the fact that ODBC fetches each record one by one.
Is that correct?
The following article seems to say that 'SQL_ROWSET_SIZE 1' is default.
http://msdn.microsoft.com/en-us/library/ms811006.aspx
Does that mean I can change it to a higher number and get better results?
My question: Is there some way to make ODBC fetch all records faster?
I have no need of fancy cursors (like DYNAMIC), I just want the records returned from my query faster.
Thanks for your time.
Regards,
Erwin Moller
.
- Prev by Date: BLOB insertion takes too long time
- Next by Date: Re: BLOB insertion takes too long time
- Previous by thread: BLOB insertion takes too long time
- Next by thread: ERROR [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Inval
- Index(es):
Relevant Pages
|
Loading
