ODBC / mssql very slow
- From: Erwin Moller <Since_humans_read_this_I_am_spammed_too_much@xxxxxxxxxxxxxxxx>
- Date: Wed, 15 Apr 2009 15:30:39 +0200
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
--
"There are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies, and the other way is to make it so complicated that there are no obvious deficiencies. The first method is far more difficult."
-- C.A.R. Hoare
.
- Prev by Date: Re: UNPIVOT to result with multiple columns
- Next by Date: Select Distinct Days and Count for each day
- Previous by thread: UNPIVOT to result with multiple columns
- Next by thread: Select Distinct Days and Count for each day
- Index(es):
Relevant Pages
|
