Hello,

Back at work we have a table with two columns long <id> and Sting <Name> in the Sybase database with millions of records, and out of which less than 0.5 records might get updated/added during the day
And we have another table table in MS SQL where we have the similar table with long <id> , String <Name>, Timestamp <insert/update>.

All the updates/inserts from Sybase needs to be replicated to MSQL. Right now we have a job which downloads all the records from first table, load-it into temp table in MS SQL and update the table accordingly and it runs several times a day.

Since we are changing only 10% of the total records, I think that using Hibernate with detached objects would be a better option than unloading / loading and then updating the entire table of 6M records

However I have few questions on how to make this work.

Initially I can save the current version of Sybase in Hibernate as objects and then for every run compare the objects against DB and perform a SaveorUpdate by iterating the result set
- But how I take advantage of Hibernate only to select and iterate only through those records that are different from my second level cache?
(Is there any way to use Query Level Cache to get the chunks of data that modified records in it?)

As of now, I am downloading the entire Sybase table into Hibernate 2nd Level Ehcache by using Manual indexing on table with the index-id on ID<long>

So what would be a better approach to synchronize this data with the data from Sybase and then perform a SaveorUpdate on MS-SQL?


Thanks,
Lakshmi Gonji