SQL Server Linked Server to Oracle slow --> OpenQuery Solution

[Rx_]

This totally surprised me. Just wondering if anyone else had this experience?

Set up the Oracle ODBC service and created Oracle Linked Servers (read-only) on the MS SQL Server 2012 database. Then, set up Views in the SQL Server Database using scripts. Had to use scripts because the 4 part naming wouldn't work as discussed on another question.

The Oracle Databases are several big states away maybe 1,500 Km away over a VPN. Any query involving the Oracle tables (or views) took a horrible 18 seconds of waiting, then all the data appeared. Our Access forms were programmed to disable until the records returned with a "please wait" message. Otherwise users thought the system locked up.

Today, an Oracle table there was a new view. The Oracle view was half Oracle half my shared SQL Server. My MS SQL Server has a "date" field for when the view was created. Got an error:
Msg 7354, Level 16, State 1, Procedure odm_TESTThenRecycle, Line 5
The OLE DB provider "OraOLEDB.Oracle" for linked server "DATA_DIV.TESLA3D.NET" supplied invalid metadata for column "DATEDATAPULLED". The data type is not supported.

Searching, a post on a 2012 SQL Server Central said to use CREATE VIEW and that this would also be much faster for an Oracle shared server.

The results were 'Instant'. No delay, just instant response.
REALLY!
Another MSDN article claims this method is like Remote Terminal. This runs on the Oracle server and doesn't bother with data type.

My problem will be that some fields (FK) come back from Oracle as text. I typically cast them as Integers (or Longs).

But, the lack of delay is just amazing.
Has anyone else run into something like this?

CREATE VIEW [dbo].[TestThenRecycle]
AS
SELECT* from OPENQUERY([Data_DIV.Tesla3D.Net], 'SELECT * from I_DBA.DATA_MIG')
--FROM [Data_DIV.Tesla3D.Net]..I_DBA.Data_MIG
GO

 

[CJ_London]

it's not an environment I've been involved in, but something I will keep in mind if I have a similar situation. Have you tried recasting one (or more) of your slow queries to see if it makes a difference?

 

[Rx_]

Yes, tried so many things. The Oracle side is a total mystery to me. I just found out today that one of the servers is a Test Server. So, I have to go rewrite all of my scripts today.

The Network IT manager asked me to change my scripts to use IP address instead of the Tesla3D.Net service. They are also trying to figure this out.

Breaking News: I tried this process on a different table that is probably residing on a completely different physical machine. The process described above was twice as SLOW.
Bad news for me. Trying to be somewhat consistent in the scripting. The process above did fix the "invalid metadata" error. But, it was slower for another one.

I may be revisiting to update a few more things. Even the Microsoft sites say "if this unless that" for making the decisions of the example above.