in reply to dbh->disconnect or leave to scope

Why not just disconnect it?

You probably know exactly when you're done with it, otherwise you may need to re-examine your design. Keeping the connection open longer than you need it is a waste of resources somewhere, somehow.

For long-running programs it can be tempting to just "keep it around" but unless your design requires that you hammer the server with a constant stream of queries it's probably better to make your conversations with the server short and to the point.

Establishing a new TCP connection with MySQL isn't that expensive, unless we're talking about thousands of connections per second I doubt that's where your bottleneck will be.

...that's my personal opinion anyway.

-- Time flies when you don't know what you're doing

Replies are listed 'Best First'.
Re^2: dbh->disconnect or leave to scope
by Cagao (Monk) on Jan 18, 2011 at 14:05 UTC

    We probably have 200 methods that connect to the db to do something, it's a pain having to $sth->finish and $dbh->disconnect at the end of every one, if they're both going to be called anyway when I return from the sub.

[reply]

      Only if $dbh is scoped to the sub. If $dbh is scoped to the file or the module, it is not disconnecting at the return from the sub.

      Personally I never disconnect, which has several reasons:

      • Most of my script/programs use the database from start to finish, so there is no use in disconnecting half-way through.
      • Connects to remote databases is very costly, so I rather have the database handle survive as long as possible.
      • Cleaning up my handles, certainly for prepared statements and cursors, should already give the server enough room to clean up for my session. Only having the session/handle open won't hurt the server too much. Note that I am using the handle, not just keeping it open to pester the server.
      • I am also working with a database that has a very nasty bug in opening a new handle to the same database (for some versions of this database server). It will simply die (in very different variations of die ranging from a database crash to a script exit). As I mostly script for database-agnostic processes, I rather prevent this from happening.

      Personally, I think scoped cleanup is the neatest way to manage database resources, but YMMV. The local DBA certainly should have a word in the matter.

      FWIW I manage two DBD's on CPAN and am co-maint of DBI.


      Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]

      Why would you call finish? From the DBI docs - Indicate that no more data will be fetched from this statement handle before it is either executed again or destroyed. You almost certainly do not need to call this method. I thought the only useful case for finish is when you absolutely know there are more rows you have not read and you know you are not going to read them. If I had not read all rows but intended to that would be a bug in my code and calling finish might mask that.

      As for not disconnecting and letting scope handle it I'd just warn you that this can be a pain to debug if your code is wrong e.g., if you fail to commit a transaction in part A of your code and the connection handle goes out of scope in part B of your code and DBI rolls it back. You'll get a warning of the rollback when the handle goes out of scope at B but the problem code is at A.

[reply]
Re^2: dbh->disconnect or leave to scope
by Anonymous Monk on Jan 20, 2011 at 00:15 UTC
    Probably not your case, but when you have long-persisting connections against MySQL from a long-running process, there are automatic timeouts from the server that will eventually hose your connection.
[reply]

In Section Seekers of Perl Wisdom