Re: Low Performance for big hospital server ..

Dawid,

> Ahh, the huge update.  Below are my "hints" I've
> found while trying to optimize such updates.
> Divide the update, if possible.  This way query uses
> less memory and you may call VACUUM inbetween
> updates.  To do this, first SELECT INTO TEMPORARY
> table the list of rows to update (their ids or something),
> and then loop through it to update the values.

There are other ways to deal as well -- one by normalizing the database.
Often, I find that massive updates like this are caused by a denormalized
database.

For example, Lyris stores its "mailing numbers" only as repeated numbers in
the recipients table.   When a mailing is complete, Lyris updates all of the
recipients .... up to 750,000 rows in the case of my client ... to indicate
the completion of the mailing (it's actually a little more complicated than
that, but the essential problem is the example)

It would be far better for Lyris to use a seperate mailings table, with a
status in that table ... which would then require only *one* update row to
indicate completion, instead of 750,000.

I can't tell you how many times I've seen this sort of thing.   And the
developers always tell me "Well, we denormalized for performance reasons ...
"

--
Josh Berkus
Aglio Database Solutions
San Francisco