Thread: Evaluating client processes vs stored procedures

List,

I have a client process that is causing some performance issues with my app. On my current platform, the standard
automatedprofiling tools are not available. Consequently, I can't easily determine where the bottleneck is in the
process.

I have an inkling that rewriting the client process to a stored procedure (implemented in C), will give at least a
modestspeed boost. 

What I'm trying to evaluate is the risk proposition of my effort. I really need a big bang for the buck, and I'm
willingto risk a week of effort if the general consensus is that it's a good bet. I estimate a week to re-implement as
astored procedure, vs about 3 to manually profile what I need. Will I find out in 3 weeks what I already suspect? If I
doprofile it and fine-tune the slow points, would the newly architected client STILL run faster as a stored proc? 

So - any sage words of wisdom for those who've been down this path? I realize that w/o specifics no one can say for
sure.General feelings from those with experience are welcome. 

Process specifics:

Currently, the client process is run once a night. It's run on a separate machine than the database. Values in one
tableare updated by evaluating user defined functions against values in another table. Here's the pseudo-code: 

For each formula_row in formula_table:
   newValue = evaluate(formula_row.body)
   update formula_row set value = newValue where sid = formula_row.sid


The evaluate function is a typical lexx/yacc parser. The formula body may refer to other formulas in the table, or to
constantvalues in another table. 

If I do implement it as a stored proc, I'd likely add triggers so that the formulas would be re-evaluated once values
inthe constants table get updated. 

During your process loop -- when / where are the updates committed ? all at
the end ?

How may rows (approx) are you updating ?

FWIW:
I think you will probably find that it is NOT the SQL update that is your
bottleneck.... I am inclined to speculate that the performance issue is
related to the size of the loop or related to when / where you commit your
updates...




"Tim Hart" <tjhart@mac.com> wrote in message
news:4684903.1144871648075.JavaMail.tjhart@mac.com...
> List,
>
> I have a client process that is causing some performance issues with my
> app. On my current platform, the standard automated profiling tools are
> not available. Consequently, I can't easily determine where the bottleneck
> is in the process.
>
> I have an inkling that rewriting the client process to a stored procedure
> (implemented in C), will give at least a modest speed boost.
>
> What I'm trying to evaluate is the risk proposition of my effort. I really
> need a big bang for the buck, and I'm willing to risk a week of effort if
> the general consensus is that it's a good bet. I estimate a week to
> re-implement as a stored procedure, vs about 3 to manually profile what I
> need. Will I find out in 3 weeks what I already suspect? If I do profile
> it and fine-tune the slow points, would the newly architected client STILL
> run faster as a stored proc?
>
> So - any sage words of wisdom for those who've been down this path? I
> realize that w/o specifics no one can say for sure. General feelings from
> those with experience are welcome.
>
> Process specifics:
>
> Currently, the client process is run once a night. It's run on a separate
> machine than the database. Values in one table are updated by evaluating
> user defined functions against values in another table. Here's the
> pseudo-code:
>
> For each formula_row in formula_table:
>   newValue = evaluate(formula_row.body)
>   update formula_row set value = newValue where sid = formula_row.sid
>
>
> The evaluate function is a typical lexx/yacc parser. The formula body may
> refer to other formulas in the table, or to constant values in another
> table.
>
> If I do implement it as a stored proc, I'd likely add triggers so that the
> formulas would be re-evaluated once values in the constants table get
> updated.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>