Re: How to make update rapidly?

On Feb 20, 4:03 am, heweiwe(dot)(dot)(dot)(at)gmail(dot)com (hewei) wrote:
> table:
> CREATE TABLE price (
> TIMESTAMP Timestamp NULL,
> id numeric(5,0) NOT NULL,
> price numeric(10,3) NULL,
> primary key (id)
> );
> sql:
> update price set price=* where id=*;
>
> On Feb 20, 2008 11:56 AM, Webb Sprague <webb(dot)spra(dot)(dot)(dot)(at)gmail(dot)com> wrote:
>
> > Post the table, the query, and the explain output, and then we can help
> > you.
>
> > On Feb 19, 2008 7:38 PM, hewei <heweiwe(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> > > Hi,Every body;
> > > I have a table contains 100,000 rows, and has a primary key(int).
> > > Now ,I need to execute sql command like "update .......... where
> > id=*"(id
> > > is primary key).
> > > I expect execute 1200-1600 sqlcommands per second(1200-1600/s).
> > > In test,when the id increase by degrees in sqlcommands, then I can
> > reach
> > > the speed(1600/s);
> > > But in fact , the id in sqlcommands is out of rule, then the speed
> > is
> > > very slow, just 100/s.
> > > what can i do? can you help me ?

You really should only use integer/serial for a primary key or bigint/
bigserial if you have a huge amount of records. From the manual on
numeric data types:

The type numeric can store numbers with up to 1000 digits of precision
and perform calculations exactly. It is especially recommended for
storing monetary amounts and other quantities where exactness is
required. However, arithmetic on numeric values is very slow compared
to the integer types, or to the floating-point types described in the
next section.

Numerics are (AFAIK) actually stored as strings, and require special
considerations when being worked with. They are also variable
length. All of this makes them slow. unless you have a REALLY good
reason for your primary key to be a numeric, use int or bigint
instead.