Data flow 'cascade style' using two linked servers (ORACLE > SQL > SQL)

Volker Source

I do need an appropriate approach to transport content of some tables this way: Data are contained in Oracle-Database, that is not under my controll. Admins set up views there for me. I can connect to MS SQL in same data center (in USA). I do have linked server, already up and running. I call this Database "Buffer" In target SQL server (in Germany) I already set up linked server to Buffer database.

All linked server up and running.

I want to have at least copy of Oracle-Database content in target MS SQL to use it for queries etc.

My approach is like this:

  1. View in Buffer-Database showing content of Oracle-Database
  2. Use TRUNCATE and INSERT to put content of view into table in Buffer-Database, no joins, all columns (execute it automatically by maintenance plan)
  3. In Target database I have view to table in Buffer-Database , no joins, all columns
  4. In Target database I have query using MERGE to put content into table in target database
  5. Have views with joins, grouping, etc. to get final desired result

All steps bring desired result, but take surprising long time: I need to access 4 tables, 3 have approx. 300.000 rows, 1 has 1.200.000 rows Putting all rows of a view into a NEW, non existing table running thru linked server it takes 2..3 minutes which feels quite o.k. Doing the MERGE it take 15..30 minutes, which is not o.k. I think

All tables miss a unique key I can rely on. However with two columns (id number + area code) I have something good enought to identify each row.

I now wonder:

  1. Is approach good in general
  2. Could it be that reading and writing in this cascade style may cause that the queries block each other
  3. At which point does it make sense to create an index (clustered on those two columns, unique or non-unique)?
  4. Should I add an own unique (int) Key and rely on that? At what point in my cascade does it make sense?


comments powered by Disqus