So if I undertstand this correctly, you have a table with a lot of rows.

There is a process which runs and updates a state(/status) in the table.

There is a second process which runs (when there are free cycles) and
processes rows whose state/status has been set to something.

so row gets inserted with status of 'N' (not)
process updates it to 'R' (ready)
second process grabs 1000 (or so) 'R'eady rows and processes them
prehaps the second process goes back and sets the state to 'P' (processed)

This sound about right? And you want to handle only getting 1000 (or
whatever) rows at a time for the secondary process to handle?

If so, it's inherently slow from a batch processing standpoint, because you
are performing updates against a large table thousands of times. If you
have any control over the processes/tables try something like:

If instead of updating the large table, move the state/status field out of
the table (create a view that looks just like the original table if it must
be there). So now you have your original table, and a secondary
'state/status' table which contains only the primary key and the state
column - and/or whatever else needs updating. If you must still update it
you can, although inserting new states may be faster, then you'd also have a
record of every state the row went through. Now it becomes a question of
tuning the large table for efficient reads of small batches (hint, you want
all of the rows you want to be read in as few reads as possible - so figure
out how to physically group the rows together so that you are getting
multiple rows for every page read. You'd also want to take care of this
little 'state' table to keep it small and functioning efficiently. Perhaps
groups of these tables based on a date/time criteria, or fragmentation to
achieve the same thing.

If you're not allowed to change the schema of the table - don't - just don't
use those columns any more.

At least that's a direction I would try. Without the schema and the code
I'm shooting in the dark.

My goal would be to avoid updating anything and to do deletes only when it
comes time to purge data.

cheers
j.

----- Original Message -----
From: "hobbes" <pasdespam@yahoo.fr>
To: <informix-list@iiug.org>
Sent: Thursday, February 12, 2004 10:42 AM
Subject: Re: update with clause first


>
> "Jack Parker" wrote
>
> > And then ....(cut)..... They have ruined the internet.
> Absolutly ...
> That maybe not only the "spam things" that was responsible of all of this
> (Communication business play a big role in it ... web page take "hours"
for
> loading now because a latence time of the advert server.)
> All thoses data sended to you , returned to the ad. sended back ,
> ect.ect.....
>
> Anyway... Internet is no more like it used to be.... but that the
evolution
> =====================================
> > Arnaud,
> >
> > Replying from work, via webmail, so the reply doesn't work as well as my
> > normal mail reader. Look for "-" in front of where I wrote.
>
> No Problem
> As theres is an answer
>
> > Sorry - should have been more careful. The rownum is, of course, not
> there
> > in Informix, but you could add a serial column to the table entitled
> > 'rownum', and it will serve the same function.
>
> Well i can't touch the schema of the database....
> I think i will write a C function, or me be a procedure that will
intercept
> the thing....
> Yes, I will think of that
>
> <cut>
>
>
> - can you describe what the application does? Maybe I could offer a
> suggestion if I understood it better.
>
> Well , that an warehouse management (i think you know about warehouse
> )
>
> While the database is update by one task.
> Another one is tracking the state (trigger ,and all thoses things)
> and then read the table and start packing, preparing, etc..... all
> warehouse needed.
>
> 1) task one => update states in many row
> that's asking to task two to take care of these and start work on it.
> 2)when task two finish => task one know that and restart to update data
when
> there is
> some time machine to work with it.
>
> => the table updated can contain many many rows.
>
> I think , that i need to rewrite a part of this code , for any databases
> instead of trying to make informix deal with it.
>
>
> >
> > > Although I've only advertised it about 1200 times on this group -
OTOH,
> I
> > > wrote it so I wouldn't have to walk through it 1200 times.
> >
> > sorry for that.... and thk you for taking time to post it once again
> ...and
> > maybe again and again and again ..... )
> >
> > - I just meant that I've probably flooded the market - people are
probably
> > tired of my mentioning the article.
>
> I swear, first time i'd heard about
>
> > - At one time it was fairly popular, I don't see a lot of people using
it
> > anymore.
>
> that's what i guessed ... sadly for me
>
>
>


sending to informix-list

"Jack Parker" wrote
>
> So if I undertstand this correctly
[cut]
> This sound about right? And you want to handle only getting 1000 (or
> whatever) rows at a time for the secondary process to handle?

This sound perfect you understand correctly
I don't know if it is my nonsenses explanations or if you're a great analyst

But that it !

> If so, it's inherently slow from a batch processing standpoint, because
you
> are performing updates against a large table thousands of times. If you
> have any control over the processes/tables try something like:
>
> If instead of updating the large table, move the state/status field out of
> the table (create a view that looks just like the original table if it
must
> be there). So now you have your original table, and a secondary
> 'state/status' table which contains only the primary key and the state
> column - and/or whatever else needs updating. If you must still update it
> you can, although inserting new states may be faster, then you'd also have
a
> record of every state the row went through. Now it becomes a question of
> tuning the large table for efficient reads of small batches (hint, you
want
> all of the rows you want to be read in as few reads as possible - so
figure
> out how to physically group the rows together so that you are getting
> multiple rows for every page read. You'd also want to take care of this
> little 'state' table to keep it small and functioning efficiently.
Perhaps
> groups of these tables based on a date/time criteria, or fragmentation to
> achieve the same thing.

Could be one solution...
Need to see if it have "side-effect"

> If you're not allowed to change the schema of the table - don't - just
don't
> use those columns any more.
> At least that's a direction I would try. Without the schema and the code
> I'm shooting in the dark.
And it's a good shot !

> My goal would be to avoid updating anything and to do deletes only when it
> comes time to purge data.

Yes i would certainly be more efficient.
I will try to code a different way for handling the data anyway

Thk you for your time !!

Arnaud