very slow performance using WHERE on Oracle linked server

  • 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 😀

     

     

     

     

  • .

    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

  • 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

  • cphite wrote:

    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.

  • This was removed by the editor as SPAM

  • 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!

  • This was removed by the editor as SPAM

  • cphite wrote:

    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!

    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.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Frederico,

    Do you have a sample script with oracle bulkcopy that I can use in SSIS?

  • Jo Pattyn wrote:

    Frederico,

    Do you have a sample script with oracle bulkcopy that I can use in SSIS?

    I'll see if I can find one - I know I have some outside SSIS, but within SSIS I think most if not all are using SQL Bulk copy.

    coding would be similar though.

  • Jo Pattyn wrote:

    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

     

    Attachments:
    You must be logged in to view attached files.
  • This was removed by the editor as SPAM

  • Thanks Frederico for the demo. I'll try it out

Viewing 15 posts - 16 through 30 (of 40 total)

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