[GENERAL] Stored procedure issue

[Dragan Zubac]

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 ?

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 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Stored procedure issue

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/01/07 20:40, Dragan Zubac 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 ?

Are you using transactions?

Are the tables properly indexed?

Are the queries in the SP using the indexes properly?

Did you do all the testing on a tiny database.

Is the SP written as efficiently?  (Think of ways to refactor it in
order to get the same results with less effort.)

- --
Ron Johnson, Jr.
Jefferson LA  USA

%SYSTEM-F-FISH, my hovercraft is full of eels
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHUh9nS9HxQb37XmcRAjPTAJ4jRUZUaF+j2KAB3+lBY6A3ROfynACfawWT
0QN026Ncl/Iag2M6E1kfjUg=
=RlXy
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly