Modifying the fetch size for Linked Server

• Ram

  Old Hand

  Points: 353

  More actions

  June 9, 2025 at 8:29 pm

  #4605099

  Hi,

  Problem: Pulling several million row table from Oracle DB into SQL server via a Linked Server takes too long: 28M rows and 2GB of data from Oracle to SQL server took 17 hours. (However we are able to scp via OS much faster: we get 3.5MB/s to 8.5MB/s; we transfer 3Gb file in about 6 to 10 mins, 6.5GB file in 30 mins - we did this test multiple times) We are thinking of setting the Fetchsize for the associated linked server high. However no matter what syntax we use, if we set the Fetchsize for the Linked Server it does not work, testing the linked server connection fails.

  Here is the linked server create syntax:

  EXEC master.dbo.sp_addlinkedserver

  @server = 'PROD_SRV',

  @provider = 'OraOLEDB.Oracle',

  @srvproduct = 'Oracle',

  @datasrc = 'ORCL',

  @provstr = ' FetchSize = 10485760; '

  SQL server 2017

  We tried with no semicolon, adding another parameter, etc. If we set a value for the provstr during the creation the Linked Server does not work. Any idea on how to set it?

  Thanks a lot, Ram

• frederico_fonseca

  SSCoach

  Points: 16245

  More actions

  June 9, 2025 at 8:47 pm

  #4605116

  there is something you can try which makes a HUGE difference on such transfers.

  don't transfer directly to the final table - transfer to a staging table (#temp or just normal table) - but the trick is that you transfer all numeric/datetime datatypes as strings using the to_char functionality (dates to yyyymmdd hh:mm:ss, and numbers to 9999.999-) - and you do it using openquery functionality instead of select ... linkedserver..table.

  and when data is on the staging table you just do a normal insert and convert the data as needed for correct datatype.

  issue with direct transfer is the conversion from oracle Data Types to SQL Datatypes - it is a huge bottleneck on these transfers.

  • This reply was modified 1 month, 3 weeks ago by frederico_fonseca.