NYCPHP Meetup

[Glenn]

maybe cooking up something with manual locks and handler
would help?

http://dev.mysql.com/doc/refman/4.1/en/handler.html

glenn

On Oct 27, 2005, at 11:52 AM, Hans Zaunere wrote:

>
>
> 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
>
>
> _______________________________________________
> New York PHP Talk Mailing List
> AMP Technology
> Supporting Apache, MySQL and PHP
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org
>