NYCPHP Meetup

[Hans Zaunere]

Matt Roberts wrote on Monday, October 24, 2005 2:48 PM:
> > I'd need more details to truly diagnose this (or even determine if
> > there's something wrong).
> 
> Thanks Hans,
> 
> I did some performance tuning around my queries based on advice from
> this:
> http://www.databasejournal.com/features/mysql/article.php/1382791 
> 
> It seems to have sped up the routine significantly.
> 
> To boil it down to a "best practice" question: of the following two
> routines, what is preferable in the general case?
> 
> 
> Process A:
> ---------------------
> Connect to mysql
> for (~ 10k iterations){
>     Query larger (500k rows) table in mysql
>     Insert into same mysql
> }
> Disconnect from mysql
> ---------------------
> 
> Process B
> ---------------------
> for (~ 10k iterations){
>     Connect to mysql
>     Query larger (500k rows) table in mysql
>     Insert into same mysql
>     Disconnect to mysql
> }
> ----------------------
> 
> 
> 
> I've generally seen process A in all books I've read, but in my
> particular example process B *seems* less error prone (but as you say
> Hans, it may depend on many other details I'm leaving out here)

'A' is the way it should be done.  If things are slowing down as the process
goes on, then there's likely some type of memory/resource leak somewhere.
When implementing process A it'll be important to close/free the various
resources and handles that get created.  While PHP does do automatic garbage
collection, I've found that when doing intensive long running operations,
the best-practice of freeing resources is more important.

> However, B looks kind of dumb and expensive to me - how costly is the
> connect / disconnect operation?

It is.  Connection buildup/tear-down with MySQL is probably the lightest of
any RDBMS, however there is overhead of course.  If you were using Oracle,
for example, you'd see a considerable delay during connect/disconnect; so
much so, that you'd likely not even consider it an option.


---
Hans Zaunere / President / New York PHP
   www.nyphp.org  /  www.nyphp.com