Update BIG Table from data in SMALL Table 1

[GeppoDarkson]

Hi to all,
I have a table containig 60 million of records containing 15 years of temperature data.

This table should be update every 15 minutes with records containing the temperature of the two last days (the new 15'+ the last 48 hours, in case of some data have changed by some external validation procedure).

The structures of BigTable and SmallTable are the same:
IdStation, DateAndHour, tValue.

What is the fastest way to perform this operation?
The following are too slow:
********************
Update BigTable B Set tValue=(Select tValue From SmallTable S Where B.IdStation= S.IdStation
And B.DateAndHour= S.DateAndHour)
Where Exist (Select tValue From SmallTable S1 Where B.IdStation= S1.IdStation
And B.DateAndHour= S1.DateAndHour)
(...And...)
Insert into BigTable
Select * From SmallTable S Where Not Exists(Select *
From BigTable B Where B.IdStation= S.IdStation
And BDateAndHour= S.DateAndHour)
********************
I've tried also this:

Update(Select B.tValue V1, S.tValue V2
From BigTable B Join SmallTable S On B.IdStation= S.IdStation
And BDateAndHour= S.DateAndHour)
Set V1=V2
Where V1<>V2
********************
...But performance are still slow.

Any suggestion?

Thank You.
Ciao,
GeppoDarkson.

 

[sem]

merge into BigTable B
using (Select IdStation,DateAndHour,tValue From SmallTable) S
on  B.IdStation= S.IdStation
       And B.DateAndHour= S.DateAndHour
when matched then update set B.tValue = S.tValue 
when not matched then insert values(S.IdStation,S.DateAndHour,S.tValue)

I hope you have an index on BigTable(IdStation,DateAndHour). If it's so you should check that it's used.

But you may also mark in some way (in trigger?) changed records in SmallTable and that ones already "posted" to avoid unnecessary processing.

Regards, Dima

http://seminihin.narod.ru

 

[GeppoDarkson]

Thank you Sam!

Off course I've got an index on Big Table.

I'm twice happy because the SmallTable can't have index, primay key and triggers because I use a Delphi component to insert records that uses OraLoader to put data into SmallTable: very fast but with this limitations.

I would offer one beer to you, but for technical problems i can just give to you a star, and my gratefulness.

Grazie,
GeppoDarkson.