Setting up a query using prepare, execute, and bind_columns() is a little
more effort than some of the helper styles, but I always use that method
because that allows me to use the same boilerplate when fetching 1 row or 1
million. Once you get used to it there isn't that much more effort. The
point in the Cheetah book is that the efficiency advantage becomes
overwhelming when processing lots of rows.
The same goes for placeholders versus pasting the values into SQL with the
additional advantage of not having to worry about quoting problems.
If you are having your module automatically generate the SQL and the
associations between the query and the application or object variables the
advantage of a consistent interface should be substantial.
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: "Karger, Amir" <[EMAIL PROTECTED]>
To: "DBI Perl mailing List (E-mail)" <[EMAIL PROTECTED]>
Sent: Friday, June 22, 2001 11:19
Subject: bind_columns and other optimizations
> Re the recent email about using bind_columns because it's more efficient:
>
> I was going to ask why binding would be more efficient, then I RdTFM and
> realized that the person was talking about binding OUTPUT, not input, and
> now I've learned something new, so thank you.
>
> The cheetah book says that bind_columns is really useful when you've got
> lots of rows o' data, because you don't have to assign the variables a
> million times, since you've got those references to play with.
>
> I'm actually writing an API to access a database, where all of the DBI is
> hidden inside my methods. (Yes, I know I should be using {Alzabo, Tangram,
> SPOPS, ....}) Am I right in assuming that here bind_columns isn't as
useful?
> I'm never going to have more than a few rows (definitely < 100) per SQL
> statement, but my statements may get called many times.
>
> For this sort of application, are there any other optimizations I should
be
> aware of? I've already figured out, for example, that within my methods,
> using prepare_cached is almost always a good idea. I got a 100% speedup by
> changing prepares to prepare_cacheds. (I'm using finish so I don't get the
> "active $sth" warning on a select. I realize that if I have too many
> statements, I could break the max. open cursors limit. If that happens, is
> there a guideline for which statement handles don't need to be cached?
Given
> that I don't know who's going to call which functions more often, so I
can't
> know (although I can guess) which handles will be used more often?)
