Re: I need to update 30.000 rows - it must be fast.

On Thu, 7 Mar 2002, forsberg_martin_at_hotmail.com wrote:
> Here is my problem:
>
>
> An application updates a table in Oracle (8.17 on win2000).
>
> The application comunicate with Oracle trough ODBC.
>
> Every second minute the value is updated for 30.000 diffrent id's.
>
> I must check if the VALUE is between MIN AND MAX. If the value is out
> of range the
>
> value must be inserted anyway, but I need to know if the value is out
> of range.
>
>
> TABLE_1
> ---------
> ID
> MAX
> MIN
> VALUE
> ALARM (Now I use a trigger: ALARM = 1 if VALUE out of range)

I'm a bit confused by this. Are you saying that you are passing 30000 update statements across the wire in one second, or are you saying that these 30000 ids are somehow related and can be grouped together somehow? From the fact that each id has its own min/max and alarm it seems they can't be grouped and are there own entities, but 30000 updates in a second through ODBC doesn't seem possible as well. You did mention minutes as well. Now that sounds reasonable through any connection layer.

If these 30000 ids are related, then I would suggest you relate them with a grouping/parent table which has the value and update that instead of the 30000 children.

If they aren't related and you _are_ sending 30000 updates then Ashish's idea sounds good.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.