Re: [HACKERS] Stored procedure issue

Hello

Please find in attachment stored procedure
(proc_uni.txt),as well as description of tables
involved in calculations.
The idea for procedure is to find longest prefix match
for destination number,try to find it in table
'billing' for particular users,find the price,and
insert message into history and inqueue table,as well
as to decreace the user's balance in table 'users'.
Would it help to put all prefices,prices data in some
sort of cache and let procedure first try to match
with data from cache and if it can't find to try to
get data from table itself from hard disk ?

I'm looking for some solution where this procedure can
operate at higher loads and to leave other parts of
database operational as much as it could.

Sincerely

Pera

--- Usama Dar <munir(dot)usama(at)gmail(dot)com> wrote:

> On Dec 2, 2007 7:40 AM, Dragan Zubac
> <moroncic(at)yahoo(dot)com> wrote:
>
> > Hello
> >
> > I have a stored procedure which does the billing
> stuff
> > in our system,it works ok,but if I put in
> > production,where there is some 5-10 billing events
> per
> > second,the whole database slows down. It won't
> even
> > drop some test table,reindex,vacuum,things which
> were
> > done before in the blink of an eye. If I stop the
> > application which calls the procedure,all is back
> to
> > normal.
> >
> > We didn't implement any special locking mechanism
> in
> > the procedure,all is default. The procedure is
> > updating user's balance in table 'users'. On the
> other
> > hand a couple of 'heavy load' table has foreign
> keys
> > pointing to table 'users'.
> >
> > Is it the matter of concurency and some locking
> issue
> > or maybe the existing of all those foreign keys
> > pointing to table 'users',or maybe something else
> > which we're not aware at the moment ?
>
>
> Can you please post your procedure and explain plan
> of the SQL which the
> procedure uses to do the billing stuff . There can
> be a zillion reasons for
> the performance problems you are seeing, but the
> email does not provide
> enough information.
>
>
> >
> > Sincerely
> >
> > Pera
> >
> >
> >
> >
>
____________________________________________________________________________________
> > Be a better sports nut! Let your teams follow you
> > with Yahoo Mobile. Try it now.
> >
>
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 7: You can help support the PostgreSQL project
> by donating at
> >
> >
> http://www.postgresql.org/about/donate
> >
>
>
>
> --
> Usama Munir Dar http://linkedin.com/in/usamadar
> Consultant Architect
> Cell:+92 321 5020666
> Skype: usamadar
>

____________________________________________________________________________________
Get easy, one-click access to your favorites.
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs