Connecting to database everytime You make an instance

pipesportugal · 06-12-2010, 05:04 PM

Hello dear colleagues from the TalkPHP forum,

I am trying to build a class to read the content of all my tables.
I am calling this class record.

Everytime I create an instance of this class I am sending all the database information on the "new" sentence, meaning:

PHP Code:


			

$customer = new record("$hostname", "$user_db", "$pass_db", "$database","$record_to_pick","$table_name");

Inside the class I am performing everytime all the normal operations of accessing a database, meaning:

PHP Code:


			

public function __construct($host,$username,$password,$db,$recordtopick,$tablename)
{
$this->host         = $host;
$this->username     = $username;
$this->password     = $password;
$this->db           = $db;
$this->recordtopick = $recordtopick;
$this->$tablename   = $tablename;
$this->conectar();
} 

private function conectar()
{
$this->link = mysql_connect($this->host, $this->username, $this->password);
mysql_select_db($this->db, $this->link);
}

Most of the times, even in a very small program I am using this class many times to read several tables.

My questions are:
1) Is the fact of using this class so often, therefore connecting to the database so many times, making my programs slow down?
2) If so, how can I improve my programs?

Thanks in advance for all replies,
PP

Village Idiot · 06-13-2010, 06:55 PM

Quote:

Originally Posted by pipesportugal

Hello dear colleagues from the TalkPHP forum,

I am trying to build a class to read the content of all my tables.
I am calling this class record.

Everytime I create an instance of this class I am sending all the database information on the "new" sentence, meaning:

PHP Code:


			

$customer = new record("$hostname", "$user_db", "$pass_db", "$database","$record_to_pick","$table_name");

Inside the class I am performing everytime all the normal operations of accessing a database, meaning:

PHP Code:


			

public function __construct($host,$username,$password,$db,$recordtopick,$tablename)
{
$this->host         = $host;
$this->username     = $username;
$this->password     = $password;
$this->db           = $db;
$this->recordtopick = $recordtopick;
$this->$tablename   = $tablename;
$this->conectar();
} 

private function conectar()
{
$this->link = mysql_connect($this->host, $this->username, $this->password);
mysql_select_db($this->db, $this->link);
}

Most of the times, even in a very small program I am using this class many times to read several tables.

My questions are:
1) Is the fact of using this class so often, therefore connecting to the database so many times, making my programs slow down?
2) If so, how can I improve my programs?

Thanks in advance for all replies,
PP

It shouldn't be a problem, use mysql_pconnect to pool your connection so that don't have to continually reopen connections. Basically what that function does it it looks for a presistant connection to open with before creating one.

pipesportugal · 06-13-2010, 09:26 PM

Hi there VI,

I followed the link You gave me just to arrive to another link on the php.net website that simply disadvices people from using pconnect (persistent connections).

I was decided to change my instruction to mysql_pconnect(), but after reading the contents of that LINK, it made me think a lot, and also I have never really had a problem with my using of mysql_connect(), so I will stick to it.

Thank You for Your suggestion though,
PP

delayedinsanity · 06-13-2010, 11:26 PM

Can you elaborate on 'using this class many times'? Are you using it many times in a single page view, or just many times across the site as a whole?

If you are calling the class numerous times inside of what would be a single page view, then you may benefit from creating a singleton instance of the database class. This would avoid connecting to the database more than once per page view. If you are simply calling the class once on various pages of the site, then I wouldn't worry about it at all. Most applications create a new connection on every page load.

Village Idiot · 06-14-2010, 03:47 AM

Quote:

Originally Posted by pipesportugal

Hi there VI,

I followed the link You gave me just to arrive to another link on the php.net website that simply disadvices people from using pconnect (persistent connections).

I was decided to change my instruction to mysql_pconnect(), but after reading the contents of that LINK, it made me think a lot, and also I have never really had a problem with my using of mysql_connect(), so I will stick to it.

Thank You for Your suggestion though,
PP

I am not intimately familiar with persistent connections on a mysql server, but when I used them on an IIS server (running SQL Server 2005) it fixed the connection errors we were having. Perhaps Microsoft has done a better job implementing them.

As long as your class is not opening and closing the connection on each query, a large number won't be a problem. But opening and closing them repetitively will cause trouble for you if you get decent traffic.

sketchMedia · 06-14-2010, 01:47 PM

The main problem with persistent connection (as I recall) is that they persist beyond the execution of the script, thus if you don't close them properly after you have finished with them you will cause all of the connections in the pool to be used up and therefore giving you a 'Too many connections' type error (im sure its more in depth than that, but I'm at work atm and can't really spend any time to research it). If I remember persistent connections are useful for keeping connections open when the overhead of opening connections is large (i.e. different server for mysql than web server etc) and a few other things, but usually you can avoid using them by better structuring your solution to the problem.

This problem may be better solved by a better program design, i.e. abstracting the DB stuff to another object that persists throughout the execution and can be passed around using dependency injection to objects that need it (you could even make it a singleton if needs be, as suggested by delayedinsanity).

PHP Code:


			
$pDb = new DBConnection('localhost', 'user', 'pass', 'db');

$pRecord1 = new Record($pDb, $RecordID);
$pRecord1->doSomethingWithTheDB();

$pRecord2 = new Record($pDb, $RecordID);
$pRecord2->doSomethingWithTheDB();

Or have I missed the problem?