[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

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
0
bibi92
Asked:
bibi92
1 Solution
 
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now