4

We have a linked server (OraOLEDB.Oracle) defined in the SQL Server environment. Oracle 12c, SQL Server 2016. There is also an Oracle client (64 bit) installed on SQL Server.

When retrieving data from Oracle (a simple query, getting all columns from a 3M row, fairly narrow table, with varchars, dates and integers), we are seeing the following performance numbers:

sqlplus: select from Oracle > OS File on the SQL Server itself less than 2k rows/sec

SSMS: insert into a SQL Server table select from Oracle using OpenQuery (passthrough to Oracle, so remote execution) less than 2k rows/sec

SQL Export/Import tool (in essence, SSIS): insert into a SQL Server table, using the OLEDB Oracle for source and OLEDB SQL Server for target over 30k rows/second

Looking for ways to improve throughput using OpenQuery/OpenResultSet, to match SSIS throughput. There is probably some buffer/flag somewhere that allows to achieve the same?

Please advise...

Thank you!

--Alex

Hadi
  • 36,233
  • 13
  • 65
  • 124
Alex V
  • 305
  • 2
  • 7
  • 1
    It is normal that SSIS is faster then INSERT queries since it uses BULK INSERT, you must try to minimize logging during INSERT to improve the performance. – Yahfoufi Mar 29 '19 at 09:01

2 Answers2

0

There is probably some buffer/flag somewhere that allows to achieve the same?

Probably looking for the FetchSize parameter

FetchSize - specifies the number of rows the provider will fetch at a time (fetch array). It must be set on the basis of data size and the response time of the network. If the value is set too high, then this could result in more wait time during the execution of the query. If the value is set too low, then this could result in many more round trips to the database. Valid values are 1 to 429,496, and 296. The default is 100.

eg

exec sp_addlinkedserver N'MyOracle', 'Oracle', 'ORAOLEDB.Oracle', N'//172.16.8.119/xe', N'FetchSize=2000', ''

See, eg https://blogs.msdn.microsoft.com/dbrowne/2013/10/02/creating-a-linked-server-for-oracle-in-64bit-sql-server/

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Is this something that requires re-creating a linked server OR can be changed post-creation? Similarly, can this same type of an argument be specified for OpenRowSet, since it does not require a linked server? – Alex V Mar 28 '19 at 19:07
  • AFAIK it's drop and recreate. For OpenRowset it goes in the `provider_string`. – David Browne - Microsoft Mar 28 '19 at 19:18
  • I set up linked server with the additional FetchSize argument as follows: EXEC master.dbo.sp_addlinkedserver @server=N'TEST', @srvProduct='Oracle', @provider='OraOLEDB.Oracle', @datasrc=N'TEST', @provstr=N'FetchSize=200' ... but no diff in performance (also tried other values, 492, 496, 296)... Same result – Alex V Mar 28 '19 at 20:45
  • What about if you select from the lined server but don't write it. EG `select max(someColumn) from linkedserver..SCOTT.HR`? – David Browne - Microsoft Mar 28 '19 at 21:13
  • Check the following question https://stackoverflow.com/questions/3577102/sql-2005-linked-server-to-oracle-queries-extremely-slow – Hadi Mar 28 '19 at 23:32
  • I am using OPENQUERY, rather than the dot notation. So the entire operation is being performed on the Oracle server, before getting the data back to SQL – Alex V Apr 01 '19 at 15:04
  • As far as trying to just do the read and no write, performance is about the same < 2k rows/second. I tried several things: 1. Oracle Developer App (completely isolating SQL Server and linked server from the picture) 2. Running an Oracle PL/SQL script that dumps output to a flat file on an SSD drive. Just a select with spool, no extra filtering/processing Same perf. in terms of retrieving data... So, it is how the client connection is configured to Oracle, and I think this is where SSIS somehow sets the proper buffer/fetch size, whereas regular client connection does not do the same. – Alex V Apr 01 '19 at 15:07
  • To further research.... a simple select : `select * from openquery(AV_TEST, 'select * from MyTable WHERE ROWNUM <= 10000');` 10k rows = 1 minute. using SQL Server's Data Migration - takes a few seconds to load it into SQL. Same OLEDB Oracle provider. Same Oracle box, same SQL Server box. 5M bytes (tiny) received from Oracle via network. There is a gigabit network between the two, so it is not the bandwidth that is an issue – Alex V Jun 21 '19 at 18:43
0

I think there are many way to enhance the performance on the INSERT query, I suggest reading the following article to get more information about data loading performance.

There are one method you can try which is minimizing the logging by using clustered index. check the link below for more information:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • While both of these links are informative, they do not answer the core question: same table insert is fast with SSIS and slow with direct load via OpenQuery. So, logging, etc. - are all equal. I am suspecting buffering on the source, rather than the target. SQL Server is surely capable of writing tens of thousands of rows per sec. – Alex V Mar 28 '19 at 20:51
  • 2
    @AlexV when using SSIS import/export wizard you are not using an INSERT INTO command, it is a bulk insert operation. For these reason i mentioned the first article. In addition the second article can enhance the performane of the INSERT INTO command. – Hadi Mar 28 '19 at 21:13