On Feb 20, 2008, at 5:03 AM, 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=*;
So you have about 714us on average per query. That's not impossible,
but your hardware and database configuration need to be up to the
task. Updates are generally slower than selects, as they have to find
a spot for the new record, check constraints, write it, etc.
Your problem could be that you're using a prepared statement. For
prepared statements the query plan gets calculated when the prepared
statement is created, without any knowledge of the actual values to
look up. That can result in a non-optimal plan. EXPLAIN ANALYZE of
that query should show more. Re-preparing it after analysing the
table may improve the performance, not sure about that.
Another possible problem, as you're doing updates, is that your data
files get bloated with old rows that don't exist anymore (in your
current transaction). An update is effectively an insert and a delete
(has to be, due to visibility to other transactions - MVCC), so every
update changes one row into two. If you don't vacuum often enough
there will be many more than 100,000 rows to search through.
Added to that; if you don't analyze, the query planner is working
with outdated information and may decide on a bad plan (not a
sequential scan probably, but non-optimal still).
Additionally, if you're trying to update the same row concurrently
from multiple sessions, you're waiting on locks. Not much you can do
about that, not something you're likely to encounter in a real
situation though.
> On Feb 20, 2008 11:56 AM, Webb Sprague <webb.sprague@gmail.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 <heweiweihe@gmail.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 ?
> >
>
>
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,47c15fde233095552171742!
