Re: Stored procedure issue

-----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-----