Re: Fwd: Stock update like application

On Mon, 6 Jan 2003, Albert Cervera Areny wrote:

> This message was sended to pgsql-performance first but I can't see much
> activity there, so I'm trying it here :-) Sorry for those in both lists !
>
> Hi!
> I'm developing a small application which I'd like to be as fast as
> possible. The program simply receives an order by modem and has to give an
> answer with the products my enterprise will be able to send them. The number
> of products could be as much as 300-400 and I don't want to make my clients
> put I high time-out before the answer is sended.
>
> I do also need to use transactions as I start calculating before the whole
> order has been received and if an error occurs everything has to be rolled
> back.
>
> Under this circumstances which way do you think it would be faster?
>
> - Make a sequence for each product (we're talking about 20000 available
> products so I think it is very big but it might give a really fast answer).

I don't understand what you mean about a sequence for each product, however,
are you sure you're not worrying excessively? 20,000 records in a table is next
to nothing. Isn't communication latency/bandwidth going to be more critical?

>
> - Using standard SQL queries: SELECT the product, and if there are enough
> units UPDATE to decrease the number of available ones. (This one I suppose
> it's not very fast as two queries need to be processed for each product).

In a transaction intended to encompass the whole order you're going to find
correctness of the application introduces a row level lock, at least, for the
duration of the transaction. Perhaps that is why you are concerned with
processing time so that you can keep that 'lock out' time to a minimum for
other orders.

>
> - Using a CURSOR or something like this which I'm not used to but I've seen
> in the examples.
>
> Should I have the queries saved in the database to encrease performance?
>
> I hope I explained well enough :-) Thanks in advance!
>
> -------------------------------------------------------

--
Nigel Andrews