June 9, 2025 at 8:29 pm
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
June 9, 2025 at 8:47 pm
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.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply