Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-18-2015, 12:53 PM   #1
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,803
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Talking SQL Server Linked Server to Oracle slow --> OpenQuery Solution

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

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
The Following User Says Thank You to Rx_ For This Useful Post:
CJ_London (05-18-2015)
Old 05-18-2015, 03:07 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,326
Thanks: 40
Thanked 3,666 Times in 3,535 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: SQL Server Linked Server to Oracle slow --> OpenQuery Solution

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?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 05-19-2015, 08:25 AM   #3
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,803
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Re: SQL Server Linked Server to Oracle slow --> OpenQuery Solution

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.

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
Reply

Tags
delay query , odbc , oracle , shared server

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] MSSQLServer Linked Server to Oracle - Strange Behavior after updating SQL settings Rx_ SQL Server 0 10-22-2014 08:50 AM
Issue while running an update query using Oracle linked server data Rajaram Queries 3 06-27-2013 02:22 PM
Access 2007 sql server linked database closes slow payout1985 General 4 08-23-2010 02:03 PM
Question Slow access 2003 search over ODBC SQL Server linked tables eligio General 9 11-21-2008 11:32 AM
Oracle, DB2 or SQL Server? WindSailor General 1 04-19-2006 10:25 PM




All times are GMT -8. The time now is 10:36 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World