Re: Millions of sequence in a database to record the counter for millions of products?

Hello chao_ping,

I can think of the following alternative: use Advance Queueing. Instead of the update, let the client send a message to the queue, with the product_id as the content. The queue will be processed by one 'server' process, which performs the update. You then have the possibility to commit after each n visits. Benefit: huge reduction of the commits. Drawback: might loose some visits, in case of a crash

Alternatively, the server process can keep the counters in a pl/sql array, which is flushed periodically. More instances of the server process can be running concurrently. Advance: use of array update. Same drawback as previous one.

Kind regards,

Herman de Boer
sr consultanc
IT Consultancy Group bv.

chao_ping wrote:

>hi, Maybe this is some stupid idear, but this is the best idear i can
>think , the following is the requirement of our site:
> 1. every view to the one page(one product) is recorded, and the count
> is added by one, doing a commit.
> 2. There is 1700K pageview to recorded everyday, thus at lease 1700K
> commit to the database, every second there is about 40 commits, in
> peak time, maybe 60/second.
> 3. The counter is needed for Data Analyse, so cannot drop it. The
> developer and the manager want to delay commit, that is , commit
> after every 100(or 1000) pageview, do a commit. But there is
> difficulty with multiple middleware that do the delayed commit,
> and lock contention with one statement to update 100-200 records
> every second by different middleware servers.
>
> I think use sequence is better to do this work. Every product have a
> unique product_id, and the old way is: update products set
> view_count=view_count+1 where product_id=v_product_id; So there is
> about 1700K commit of this statement now, and more and more this
> statement these days, database is burdened too much.
>
>Now i want to: after every view to some product (one single page), just
>do a: select seq_product_id.nextval from dual; THis shows the current
>pageview of the product. But there is about 80k products online, so it
>means that i have to create 80k sequence, and with the rapid growth of
>the products online, there will be more and more products online. Every
>new product is added to the database, i generate a unique sequence name
>like 'SEQ_product_ID'; maybe someday 200k products on line, so it means
>i have to create 200k sequences, and with history products, i also have
>to keep the old sequence. This is foolish maybe, but is there any better
>way to count 80k products with their view_count? Please share your
>opnion. Thanks.
>
>
>
Received on Fri Jul 19 2002 - 03:52:01 CDT