2

I need to execute the following SQL (SQL Server 2008) in a scheduled job periodically. The Query plan shows 53% cost is sort after the data is pulled from the oracle server. However, I've ordered the data in the openquery. How to force the query not to sort when merge joining? 

merge target as t
using (select * from openquery(oracle, '
         select * from t1 where UpdateTime > ''....'' order by k1, k2')
      ) as s on s.k1=t.k1 and s.k2=t.K2 -- the clustered PK of "target" is K1,k2
when matched then ...... 
when not matched then ......

Is there something like bulk insert's "with (order( { column [ ASC | DESC ] } [ ,...n ] ))"? will it help improve the query plan of the merge statement if it exists?

If the oracle table already have PK on K1,K2, will just using oracle.db.owner.tablename as target better? (will SQL Server figure out the index from oracle meta information?)

Or the best I can do is stored the oracle data in a local temp table and create a clustered primary key on K1,k2? I am trying to avoid to create a temp table because sometime the returned openquery data set can be large.

ca9163d9
  • 27,283
  • 64
  • 210
  • 413

1 Answers1

1

I think a table is the best way to go because then you can create whatever indexes you need, but there's no reason why it should be temporary; why not create a permanent staging table? A local join using local indexes will probably be much more efficient than a join on the results of a remote query, although the only way to know for sure is to test it and see.

If you're worried about the large number of rows, you can look into only copying over new or changed rows. If the Oracle table already has columns for row creation and update times, that would be quite easy.

Alternatively, you could consider using SSIS instead of a scheduled job. I understand that if you're not already using SSIS you may not want to invest time in learning it, but it's a very powerful tool and it's designed for moving large amounts of data into MSSQL. You would create a package with the following workflow:

  1. Delete existing rows from the staging table (only if you can't populate it incrementally)
  2. Copy the data from Oracle
  3. Execute the MERGE statement
Pondlife
  • 15,992
  • 6
  • 37
  • 51
  • Thanks. Basically I need a copy of the Oracle table in SQL Server so an extra complete staging table seems too much. Will the SSIS package with the above three steps out performance the TSQL statement? The only difference is (2)? SSIS may slice the data into smaller pieces if there is a lot of new data. And will the merge data flow transformation be fast without creating the extra table? – ca9163d9 Aug 17 '11 at 20:11
  • I wouldn't use the Merge transformation in SSIS; I would just copy the data from Oracle into a permanent table and use the Execute SQL task to do a TSQL MERGE as you're already doing. The only way to answer the performance question is to try it and see, but using a permanent table will give you full control over the queries and indexes involved. I've always found linked servers and OPENQUERY to be a bit unpredictable and not at all transparent. – Pondlife Aug 18 '11 at 09:06