optimize query linked server oracle

Hello,

How can I optimize this query, the table contains millions rows :
BEGIN TRAN
GO
INSERT INTO db.dbo.TblTEST
Select * from [LINKED_ORACLE]..[dbo].[TblTEST] AS lnk
WHERE NOT EXISTS
(SELECT * FROM db.dbo.TblTEST AS loc
WHERE loc.IDXNX = lnk.IDXNX  )
GO
COMMIT TRAN
GO
Thanks
bibi92Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
The only solution: Create a view or stored procedure on Oracle.
0
bibi92Author Commented:
It's not possible to create a view or store procedure on the db oracle.
0
bibi92Author Commented:
it's a active dataguard.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ste5anSenior DeveloperCommented:
Then there is afaik no way.

Cause SQL Server needs to read the data to process it locally. This is the bottleneck.

The only other approach: can you create a table on Oracle? Then you can track what rows where already transferred.
0
bibi92Author Commented:
it's a active dataguard.  I can't. Thanks
0
johnsoneSenior Oracle DBACommented:
I never tried this, but in theory it should work.

To get any kind of improvement, you need to push your where clause to the Oracle side.  While you cannot create a new table to do this, this is what you should be able to do.

1.

Create a global temporary table on the primary, it should have the required fields from your SQL Server database to do the not exists.

2.

Once this propagates to the standby database you should be able to use it.  A global temporary table uses temp space and that is allowed in a standby.

3.

Insert the records from SQL Server into the global temporary table on the Oracle side.

4.

Issue the query on the SQL Server side using the linked tables, but now it should process it all locally on the Oracle side and only return the required rows and avoid sending the whole table across to SQL Server.
I don't have the environment to be able to test something like this, but in theory it should work.
0
bibi92Author Commented:
Hello,

I can't create table on the primary production.
Thanks
0
Scott PletcherSenior DBACommented:
One possibility:
Bring over just the key values first.  Determine the missing key values/rows.  Then copy those rows using "IN" a static list, so the list gets passed to Oracle for it to resolve.

Something like below.  If there are too many values to fit in 8000 bytes, you might have to use a loop; I'll leave that logic to you, for now at least.



IF OBJECT_ID('tempdb.dbo.IDXNXs_to_copy') IS NOT NULL
    DROP TABLE tempdb.dbo.IDXNXs_to_copy
   
CREATE TABLE tempdb.dbo.IDXNXs_to_copy (
    IDXNX int PRIMARY KEY
    )

DECLARE @IDXNX_to_copy varchar(7900)
DECLARE @sql varchar(8000)

--get the keys that need copied/are missing from the local table
INSERT INTO tempdb.dbo.IDXNXs_to_copy
SELECT lnk.IDXNX
FROM [LINKED_ORACLE]..[dbo].[TblTEST] AS lnk
WHERE NOT EXISTS (
    SELECT IDXNX
    FROM db.dbo.TblTEST AS loc
    WHERE loc.IDXNX = lnk.IDXNX  
    )

--!!MUST LOOP HERE IF too many IDXNXs need copied;
--  you could try varchar(max) for @IDDNX_to_copy, I don't know if that will working going to Oracle or not.
--BEGIN ... --loop if needed
SELECT @IDXNX_to_copy = STUFF((
    SELECT ',' + CAST(IDXNX AS varchar(10))
    FROM tempdb.dbo.IDXNXs_to_copy
    ORDER BY IDXNX
    FOR XML PATH('')
    ), 1, 1, '')

--SELECT @IDXNX_to_copy

SET @sql = '
INSERT INTO db.dbo.TblTEST
SELECT *
FROM [LINKED_ORACLE]..[dbo].[TblTEST] AS lnk
WHERE
    IDXNX IN (' + @IDXNX_to_copy + ')'
   
EXEC(@sql)
--END ... --loop if needed
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.