Thread: best practice for use of functions..

My application does between 20-40 inserts per transaction.  I am putting all
of them into functions to limit the calls from the DBI to the database.

My preferred method is to have a function for each table that I do an insert
into, it's easier to manage that way and a lot easier to make changes if I
add/drop columns and tables.  Right now I have one function that is called
by my application that in turn calls all the other functions.

I am wondering is there is a significant overhead for calling say 10
functions from within a function compared to putting everything into one
single function?  Or are there other reasons why I would want to do it
differently?

Chris

"Chris Ochs" <chris@paymentonline.com> writes:

> My preferred method is to have a function for each table that I do an insert
> into, it's easier to manage that way and a lot easier to make changes if I
> add/drop columns and tables.  Right now I have one function that is called
> by my application that in turn calls all the other functions.
>
> I am wondering is there is a significant overhead for calling say 10
> functions from within a function compared to putting everything into one
> single function?

Compared to the disk I/O overhead for a transaction, it'd be lost in
the noise--do whatever makes you happy.  :)

-Doug

Aren't there some caveats to this related to transactions?  I've been
working under the strong (I think I read.. heh) impression that
transactions cannot be nested, in 7.3 at least..

This would lead me to believe if the 'main' function called another that
failed and thus issued a rollback, would that not rollback the entire
meta-transaction, for lack of a better word?

-Allen

At 21:26 1/14/2004, Doug McNaught wrote:
>"Chris Ochs" <chris@paymentonline.com> writes:
>
> > My preferred method is to have a function for each table that I do an
> insert
> > into, it's easier to manage that way and a lot easier to make changes if I
> > add/drop columns and tables.  Right now I have one function that is called
> > by my application that in turn calls all the other functions.
> >
> > I am wondering is there is a significant overhead for calling say 10
> > functions from within a function compared to putting everything into one
> > single function?
>
>Compared to the disk I/O overhead for a transaction, it'd be lost in
>the noise--do whatever makes you happy.  :)
>
>-Doug
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster

On Thursday 15 January 2004 03:50, Allen Landsidel wrote:
> Aren't there some caveats to this related to transactions?  I've been
> working under the strong (I think I read.. heh) impression that
> transactions cannot be nested, in 7.3 at least..
>
> This would lead me to believe if the 'main' function called another that
> failed and thus issued a rollback, would that not rollback the entire
> meta-transaction, for lack of a better word?

"multi-statement transaction" might be the better word. Now need a better word
for "a bunch of words strung together" ;-)

You're correct, but in this case Chris was doing a batch of inserts in the
same transaction anyway, so that's what he wants.
--
  Richard Huxton
  Archonet Ltd