very slow performance using WHERE on Oracle linked server

Question

Post reply

very slow performance using WHERE on Oracle linked server

cphite

SSCarpal Tunnel

Points: 4278
More actions
March 15, 2024 at 1:06 pm

#4395918

I have a linked server pointing to an Oracle instance using OraOLEDB.Oracle. The table contains about 40K rows.
My issue is that while I can get fine performance doing a SELECT or DELETE with no WHERE clause, the moment I add a WHERE it is just horrible. Even selecting a single row takes 20+ minutes.
Does anyone have any suggestions?
March 16, 2024 at 10:37 am

This was removed by the editor as SPAM
March 16, 2024 at 10:54 am

This was removed by the editor as SPAM
March 16, 2024 at 2:22 pm

This was removed by the editor as SPAM
March 17, 2024 at 4:38 pm

This was removed by the editor as SPAM
March 18, 2024 at 12:42 pm

This was removed by the editor as SPAM
March 18, 2024 at 12:44 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 41 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16167 More actions · Answer 1

you saying that "select * from remoteoracle where a = b" is slow?

or is it when you are joining to local tables (big NO NO).

but regardless - access to remote oracle tables is best done using OPENQUERY in conjunction with dynamic SQL where you can pass filtering criteria to the remote server, and copying the data to a local temp table for further use by your code.

it is also advisable when doing such dynamic sql to convert the oracle DATE/DATETIME to varchar, as well as numbers, and then convert back to correct datatypes on the SQL side - performance does normally greatly improve with this trick.

This reply was modified 9 months, 1 week ago by frederico_fonseca.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

I agree with Frederico_fonsca here - OPENQUERY is going to give you the best performance as the query runs fully on the remote side. IF you are doing it in the first method they mentions OR the second, you will have performance hits.

BUT tuning a query is similar no matter how you cut it. Find the slow part of the query and work out why it is slow. IF your query is strictly selecting from the linked server, one performance "tip" that sometimes helps is to pull all the data (no filtering) to a temp table and do all the work in the temp table. You may need to add indexes on the Oracle side, or you may have blocking happening on the Oracle side and may need a query hint for that.

One thing I try to avoid doing is linked server data manipulation. I rarely, if ever, us a linked server to change data. The application layer can connect to the second system to do the data manipulation if needed. The main reason being that I've found it to be a performance suck OR I need to use dynamic SQL in an OPENQUERY command and I don't like dynamic SQL hitting a production system. There are exceptions (aren't there always?), but I try to avoid it if possible.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 3

We're not joining to anything local, fortunately. We're literally just dumping the contents of reporting tables on our server to a tables on their server - the only difference being that theirs are Oracle.

So basically, I am doing something like this - which performs just fine:

INSERT [ORACLESRV]..[BLAH].[SOMETABLE] ( COL1, COL2, COL3) 

SELECT COL1, COL2, COL3 FROM [SOMETABLE]

And if I do either of these - it performs just fine:

SELECT * FROM [ORACLESRV]..[BLAH].[SOMETABLE]

DELETE FROM [ORACLESRV]..[BLAH].[SOMETABLE]

However, if I try doing either of these, it runs really, really slowly:

SELECT * FROM [ORACLESRV]..[BLAH].[SOMETABLE] WHERE COL1 = 'meh'

DELETE FROM [ORACLESRV]..[BLAH].[SOMETABLE] WHERE COL1 = 'meh'

Currently, we just delete everything each night and repopulate - and that's been fine. But they're asking for a few new datasets that are much larger, and it takes too long to push the entire thing. So I want to delete the most recent period (year and month) and just insert that smaller set. The insert works like a charm - but the delete is taking over an hour even for small rowcounts.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 4

based on what you said I STRONGLY recommend that you change the process to use either a ETL tool (SSIS or plain C#- SSIS will likely be the slowest - ideally C# with oracle client and oracle bulkinsert objects would be the fastest) to do the transfer, and ensure that the deletes are done from within the Oracle connection.

while your inserts may seem fine now, at a later stage they are likely to grow a lot slower.

one possibility, which may work fast as well, is for you to setup a dblink from Oracle to your SQL DB, and have a Stored Proc in Oracle connect to your server to copy the data from SQL to Oracle (instead of pushing from SQL into oracle) and do all other required deletes.

for the plain selects you mention above using openquery (with dynamic sql if you need to pass parameters) as that is bound to be faster.

Esecuting a Oracle SP would also work fine using openquery.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 5

Linked servers have several problems - one of the biggest is that SQL Server will often pass the query to the other system with incorrect parameters.

We just ran into this same issue, where the query was a simple SELECT with date parameters. Unfortunately - SQL Server decided to 'filter' the datetime columns by passing the parameters as SQL_VARIANT and converting the columns to SQL_VARIANT. And this was on a SQL Server to SQL Server linked server...

Using OPENQUERY solved that problem and it did not require dynamic SQL. The code was looking for a single day - so we could hard-code the date parameters for yesterday WHERE dateColumn >= CAST(GETDATE() - 1 AS date) AND dateColumn < CAST(GETDATE() AS date).

Since you are looking to perform a DELETE/INSERT operation with 'fixed' dates - you can do the same thing, just using Oracle syntax to define the date range to be deleted. However, I would not personally go this route as you could easily end up with a failed process near the end of the month and end up with no data available until the issue has been addressed.

Rather - I would query the Oracle table for the latest row available (by date or some other key value) and that becomes the start of your range (non-inclusive) and you then set a fixed end range. If you miss a day's processing it will automatically pick up from the last load and insert all data from that point forward to your fixed end range.

This also avoid having to worry about deleting data on the Oracle side - since now you are only inserting new rows. If you ever have to update existing rows then you are going to definitely want to move this out of SQL Server and into an ETL tool. Or you will need to be able to create staging tables to load the data - and then call an UPSERT process on the Oracle system to UPDATE/INSERT 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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

cphite wrote:

We're not joining to anything local, fortunately. We're literally just dumping the contents of reporting tables on our server to a tables on their server - the only difference being that theirs are Oracle.
So basically, I am doing something like this - which performs just fine:
INSERT [ORACLESRV]..[BLAH].[SOMETABLE] ( COL1, COL2, COL3) 

SELECT COL1, COL2, COL3 FROM [SOMETABLE]
And if I do either of these - it performs just fine:
SELECT * FROM [ORACLESRV]..[BLAH].[SOMETABLE]

DELETE FROM [ORACLESRV]..[BLAH].[SOMETABLE]
However, if I try doing either of these, it runs really, really slowly:
SELECT * FROM [ORACLESRV]..[BLAH].[SOMETABLE] WHERE COL1 = 'meh'

DELETE FROM [ORACLESRV]..[BLAH].[SOMETABLE] WHERE COL1 = 'meh'
Currently, we just delete everything each night and repopulate - and that's been fine. But they're asking for a few new datasets that are much larger, and it takes too long to push the entire thing. So I want to delete the most recent period (year and month) and just insert that smaller set. The insert works like a charm - but the delete is taking over an hour even for small rowcounts.

As the others have recommended, I strongly recommend that you try using "pass through" queries using OPENQUERY. The worked a real treat for us in this area because the query is executed on the remote machine.

https://learn.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

carmellaretta Newbie Points: 1 More actions · Answer 7

carmellaretta

Newbie

Points: 1

March 18, 2024 at 11:30 am

#4397403

I'll try this.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Heh... you just had to know there was going to be some spam involved after the 2 new one-off replies that mostly didn't say much. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)