March 18, 2024 at 3:17 pm
Thanks all...
I tried using a dynamically generated openquery statement and, while it's not as fast as I was hoping, it's definitely a lot faster than before. Going to try to get the Oracle side to add an index on their end which should help even further.
In any event, it looks like a viable solution 😀
March 18, 2024 at 6:24 pm
.
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
March 18, 2024 at 6:26 pm
Are there any performance considerations or other pros/cons in using EXEC at Linked server vs. OPENQUERY?
Example: EXEC MyLinkedServer.MyDatabase.dbo.sp_executesql @SQL
March 18, 2024 at 6:33 pm
Thanks all...
I tried using a dynamically generated openquery statement and, while it's not as fast as I was hoping, it's definitely a lot faster than before. Going to try to get the Oracle side to add an index on their end which should help even further.
In any event, it looks like a viable solution 😀
which queries weren't as fast as you expected? from what you mentioned, you have a few select, a few delete and a few inserts (inserts should be a pull from Oracle, not a push from Sql - or as part of an ETL)
if you give us one of the full queries you think didn't behave as expected maybe we can add something to it.
March 19, 2024 at 6:14 am
This was removed by the editor as SPAM
March 19, 2024 at 12:55 pm
Appreciate the replies!
The script I used most recently is this:
declare @period varchar(10) = '2024MAR'
declare @innerCommand varchar(500) = 'select * from FACT_GL_ACTUALS where TRX_PERIOD = ''''' + @period + ''''
declare @outerCommand varchar(500) = 'delete from openquery ( [ORACLEDW], ''' + @innerCommand + ''''') '
exec ( @outerCommand )
insert [ORACLEDW]..[DW].[FACT_GL_ACTUALS] ( <column list...> )
select <column list...> from FACT_GL_ACTUALS
The delete is taking the most amount of time - around 10-20 minutes. I'm going to try casting the variable as a varchar within the select, to see if what Jeff mentioned is happening here.
I would prefer to have the Oracle system pull the data, as opposed to us pushing it, but unfortunately that isn't an option due to security restrictions.
Previously I tried using SSIS but I was having trouble getting it to connect to the Oracle instance; I do plan on re-trying that at some point.
Anyway, thanks again! Much appreciated!
March 19, 2024 at 2:04 pm
This was removed by the editor as SPAM
March 19, 2024 at 3:17 pm
Appreciate the replies!
The script I used most recently is this:
declare @period varchar(10) = '2024MAR'
declare @innerCommand varchar(500) = 'select * from FACT_GL_ACTUALS where TRX_PERIOD = ''''' + @period + ''''
declare @outerCommand varchar(500) = 'delete from openquery ( [ORACLEDW], ''' + @innerCommand + ''''') '
exec ( @outerCommand )
insert [ORACLEDW]..[DW].[FACT_GL_ACTUALS] ( <column list...> )
select <column list...> from FACT_GL_ACTUALSThe delete is taking the most amount of time - around 10-20 minutes. I'm going to try casting the variable as a varchar within the select, to see if what Jeff mentioned is happening here.
I would prefer to have the Oracle system pull the data, as opposed to us pushing it, but unfortunately that isn't an option due to security restrictions.
Previously I tried using SSIS but I was having trouble getting it to connect to the Oracle instance; I do plan on re-trying that at some point.
Anyway, thanks again! Much appreciated!
for the delete the issue may well be a missing index on the Oracle side - but if there is such an index, can you try instead the "exec at remoteserver" option - see https://www.mssqltips.com/sqlservertip/1757/dynamic-sql-execution-on-remote-sql-server-using-exec-at/ for example.
I never tried it with a Oracle server but still its worth a try.
Regarding SSIS and oracle - while it can work, default Oracle connector is slowish - even the MS Oracle Connector (old Attunity driver) - https://learn.microsoft.com/en-us/sql/integration-services/data-flow/oracle-connector?view=sql-server-ver16 - which requires Enterprise Edition) is slower than using C# with native OracleBulkCopy - which you can code from within a C# script/component within SSIS making it extremely easy to use.
likely for your case going the c# route (within SSIS if it makes your DBA's happier) would be the best solution - as you could then do the full deletes/inserts within C#, and just your selects from Oracle into your SQL Server db using either C# or straight linked server.
March 20, 2024 at 3:54 am
This was removed by the editor as SPAM
March 20, 2024 at 5:57 pm
This was removed by the editor as SPAM
March 21, 2024 at 3:19 pm
Frederico,
Do you have a sample script with oracle bulkcopy that I can use in SSIS?
March 23, 2024 at 4:24 pm
Frederico,
Do you have a sample script with oracle bulkcopy that I can use in SSIS?
sample DTSX package attached.
you will also need to install Oracle Client for Microsoft tools - https://www.oracle.com/database/technologies/appdev/ocmt.html
March 23, 2024 at 11:37 pm
This was removed by the editor as SPAM
March 25, 2024 at 9:10 am
Thanks Frederico for the demo. I'll try it out
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply