OPENROWSET select statement performance -- runs in 4 seconds on test server vs. 3 minutes on production server.

  • Hello,

    We have a sp that runs 3 times each day in production, taking over 3 minutes each run. The sp is using OPENROWSET to select records from a remote Oracle view to populate local SQL Server table. After running profiler trace to identify where the bottleneck is, I reduced the code to bare minimum to test and noticed that just the SELECT against OPENROWSET is taking majority of time. Running same SELECT against the remote production Oracle view I get the following results: query finishes in 4 seconds on SQL Server test server, 3 minutes on SQL Server production server; both show 'Remote Scan' in the execution plan. Remote prod Oracle view is about 70K records and SELECT pulls last 30 days, which equates to about 2500 records. SQL Server test server is a clone of production SQL Server, though not clustered, and mostly idle compared to the busy production server.

    Win2003 SP2

    SQL Server 2000 Enterprise 32-bit SP4 build 8.00.2189

    32GB RAM

    8 CPUs

    I've already done some research on OPENQUERY/Linked Servers vs. OPENROWSET performance, but I guess right now I'm questioning the difference in query run time between SQL Server test vs. production. Looking for some suggestions as to where to look next to try to troubleshoot and improve the sp performance. Any insight would be much appreciated. 🙂

    Please see sample code below...

    Thanks,

    rg

    sample of the code:

    DECLARE @qry nvarchar(4000)

    DECLARE @date varchar(255)

    DECLARE @openrowset varchar(255)

    ..

    SET @openrowset = 'SELECT * FROM openrowset(''MSDAORA'',''xxxxxx'';''xxxxxx'';''xxxxxx'',''' + char(10)

    SET @qry = @openrowset +

    'SELECT

    ID,

    ..

    ..

    ..

    FROM view_name where LAST_MODIFIED_DATE > to_timestamp('''''+@date+''''',''''YYYY-MM-DD HH24:MI:SS'''')'')'

    EXEC (@qry)

  • Is the network route the same between the dev server and oracle server and the prod server and oracle server?

    Why are you using dynamic SQL?

  • there's additional logic throughout sp, for example @openrowset is assigned to point to prod vs. test Oracle server depending on whether sp runs on SQL Server prod server vs. test server; I didn't include that since didn't think that was relevant to this post...I'm concentrating on why select takes so much longer to run against prod Oracle view when run from SQL Server prod server vs. SQL Server test server....is it memory pressure related? is it tempdb activity related? etc...

    I can certainly try without dynamic sql, just not sure that's the issue...

    thanks for your feedback... rg

  • I will check on the network route between Oracle server and SQL Server prod vs. test next week when I'm back at the office, as I questioned that too...thanks for reminder/suggestion...

    rg

  • Does the Oracle table have the same number of rows in test and prod? Does the Oracle table in test and prod have the same indexes? When you use the linked servers, are both OLE DB or is one ODBC? And, why are you selecting against a whole Oracle table instead of an Oracle view that automatically limits the number of rows you want to return?

    --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)

  • Just wanted to follow-up to above: Currently suspecting network latency, as SQL test and Oracle prod servers are located in West Coast data center, whereas SQL prod server is located in East Coast data center, doing additional tests.

    Please confirm: From what I understand how OPENROWSET works, the query is processed on remote Oracle server and the WHERE clause processed on the remote server as well, only qualifying rows are sent back to SQL Server (vs. all records being sent to SQL server and WHERE clause being processed on local SQL server)? is this correct?

    Jeff:

    1. Not relevant, as I'm testing against prod Oracle view only

    2. Not relevant, as I'm testing against prod Oracle view only

    3. Not using linked server, using OPENROWSET

    4. Already using a view, though I did confirm the view selects all records from underlying table of 70K rows, no index on LAST_MODIFIED_DATE--but would expect to see similar run-time from both SQL prod and SQL test if issue was remote Oracle prod view related. Also, shouldn't be an issue since using OPENROWSET, correct me if I'm wrong. I understand that we can add index and also modify view to include the WHERE clause (if it doesn't impact other code), but that is not the underlying issue at this point from what we can tell.

    thank you again for feedback...

    rg

  • rg (9/8/2008)


    Just wanted to follow-up to above: Currently suspecting network latency, as SQL test and Oracle prod servers are located in West Coast data center, whereas SQL prod server is located in East Coast data center,...

    Heh... yeah, that might do it. We had a million row job that took 10 minutes to run locally... took almost 3 hours remotely. The fix was to do the job locally and only transfer the answer remotely. Even that still took a bit of time.

    --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)

  • I'm sorry this is not a reply to your question...this is more a question to u guys since you guys are using the OPENROWSET and nobody has replied in the other group...

    I am trying to export data to excel file....this is the story...

    This sp works for couple of times after restarting the service and then it stops working.

    The reason for this sp is to run in a job every 8 hours....

    I did follow some instructions from MS...site and it did made sense since I saw that it is writing to this local dir.. C:\TEMP

    http://support.microsoft.com/kb/814398

    Could anyone provide me some new guide to this issue.....?

    This is the error I get when execute the sp:

    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

    [OLE/DB provider returned message: Unspecified error]

    OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

    This is my code:

    DECLARE @channel AS VARCHAR(10)

    DECLARE @feed AS VARCHAR(10)

    DECLARE @OriginalFile AS VARCHAR(100)

    DECLARE @FilePathName AS VARCHAR(100)

    DECLARE @VBSFilePathName AS VARCHAR(100)

    DECLARE @Msg AS VARCHAR(250)

    DECLARE @ReturnStatus INT

    DECLARE @DateRpt AS VARCHAR(15)

    --Drop and Create temporal tables

    IF EXISTS ( SELECT *

    FROM tempdb.dbo.sysobjects

    WHERE ID = object_id(N'tempdb.dbo.#TmpSpeIngest'))

    BEGIN

    DROP TABLE #TmpSpeIngest

    END

    CREATE TABLE #TmpSpeIngest (

    LogChannel NCHAR (15) NOT NULL,

    LogFeed NCHAR (20) NOT NULL,

    LogDate VARCHAR (50) NULL)

    --LogFiles Avaliable

    INSERT #TmpSpeIngest

    SELECT DISTINCT LogChannel, LogFeed, LogDate

    FROM tb_spe_ingest

    WHERE LogDate = @date

    ORDER BY LogChannel, LogFeed

    INSERT INTO OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=C:\Program Files\Microsoft SQL Server\MSSQL\REPORTS\ReporteCombinado.xls',

    'SELECT * FROM [AvailableLogsForReport$]')SELECT * FROM #TmpSpeIngest

    Any help is greatly appreciated....

    Regards,

    Jose

  • If someone or something has the file open when your code runs, it will give such an error message. Bouncing the server seems to clear it because the file is on the server and bouncing it forces a disconnect.

    --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)

Viewing 9 posts - 1 through 8 (of 8 total)

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