0

I have:

  • A linux server with PHP 5.3.2 and Apache 2.0
  • A windows Server with SQL SERVER

I connect my linux server with database with unixODBC 2.3.1 and FREETDS 0.9.1

The connection is okay but the queries are slow. Here is my code that generate the image below:

$this->adodb->LogSQL(true); // turn on logging
$query = "select c.name, t.name, c.length from syscolumns c join systypes t on t.xusertype = c.xusertype
          join sysobjects o on o.id=c.id where o.name = 'CONDOMINIO'";
$res = $this->adodb->Execute($query);
$this->adodb->LogSQL(false); // turn off logging
$perf = NewPerfMonitor($this->adodb);
echo $perf->SuspiciousSQL();
echo $perf->ExpensiveSQL();

Image http://www.vigoonline.net/slow.png

As you can see, the first query has an average time of 4.68 seconds, which is way too slow.

If I execute the same query like this:

$this->adodb->_query($query);

Then the time to execute the query takes less than a second what is great. Has anyone else experienced the same thing?

The query called with "Execute" is used for the own class for bringing the "MetaColumns" information from the table

The table "CONDOMINIO" only has 21 rows.

If the server database is in the same machine with the application script, then the response is fast!

Ry-
  • 218,210
  • 55
  • 464
  • 476
user1418446
  • 29
  • 4
  • 1
    I know it's traditional to put SQL query keywords in all caps, but that doesn't mean your title has to be all caps too. – Ry- May 26 '12 at 00:58
  • Are you confident the problem is _this_ query? Perhaps the initial connection or first query is always going to be slow..? – sarnold May 26 '12 at 01:01
  • Yes because if i execute with the method "_query" from ADODB , the query is executed really fast... – user1418446 May 26 '12 at 01:04

1 Answers1

1

I resolved this myself. Here is what I did:

Go to the file /adodb/drivers/adodb-odbc.inc.php and to the class ADODB_odbc

Here try to find:

var $curmode = SQL_CUR_USE_DRIVER

and change it to:

var $curmode = SQL_CUR_USE_IF_NEEDED;

This will make the connection use SQL_CUR_USE_ODBC when needed and "Voila" DB access speed to SQL server is really fast!

jonsca
  • 10,218
  • 26
  • 54
  • 62
user1418446
  • 29
  • 4