June 23, 2021 at 3:05 am
I have an application which runs on SQL 2016 & has a link server connection to an Oracle OLTP database.
The application will initiate a query which will also pull data through the link server. Currently we're facing some issues where by there is quite a bad wait time for OLEDB which points to the link server to our Oracle DB.
Is there anyway to workaround on this? I want to suggest perhaps to replicate the Oracle DB over to SQL database to avoid using any link server connection, however the Oracle DB is an OLTP which is always updated. So how to ensure that my replicated db will always have the latest data from OLTP?
June 23, 2021 at 6:19 am
linked server will always have that issue - what you need to do is to alleviate the problem.
when pulling data from Oracle to SQL one of the biggest issues is conversion of date and numbers data types - a trick that is commonly used and that normally results in significant performance increase is to do the following
use openquery to retrieve the data onto temp tables where feasible
within it and on the oracle side, convert ALL dates and numbers to strings - and then convert back to desired datatype within the sql side (use dynamic sql if required to pass parameters)
select *
from openquery(linkedserver, 'select to_char(datetimecol, 'YYYY-MM-DD HH24:MI:SS') as datetimecol
, to_char(mydecimalcol, 'fm9999999.90') as mydecimalcol
from mytbl') t1
if you use your linked server as part of a join on with other local tables this is always going to perform badly
June 23, 2021 at 3:41 pm
Another approach is to populate local tables with the previous day/month/year of data. In most cases - the queries don't need real-time up to the second data, so those can be run against the local data. For these - you can schedule an agent job to run every day to refresh the local table.
For those that need real-time data, use OPENROWSET as outlined above into a local temp table and then join to the local temp table. Don't try to join across the linked server - especially not using 4-part naming. In many cases, SQL Server cannot figure out what to request so it falls back to a cursor mode and retrieves one row at a time - or it pulls the whole table across into a worktable in temp and then joins and filters the data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply