Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OPENROWSET select statement performance -- runs in 4 seconds on test server vs. 3 minutes on production server. Expand / Collapse
Author
Message
Posted Friday, August 29, 2008 3:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 2:32 PM
Points: 237, Visits: 249
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)
Post #561539
Posted Friday, August 29, 2008 5:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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?




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #561566
Posted Saturday, August 30, 2008 2:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 2:32 PM
Points: 237, Visits: 249
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



Post #561656
Posted Saturday, August 30, 2008 2:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 2:32 PM
Points: 237, Visits: 249
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
Post #561658
Posted Saturday, August 30, 2008 4:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #561674
Posted Monday, September 8, 2008 10:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 2:32 PM
Points: 237, Visits: 249
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

Post #565603
Posted Monday, September 8, 2008 7:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #565853
Posted Friday, January 30, 2009 3:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 12, 2011 1:21 PM
Points: 3, Visits: 64
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


Post #647370
Posted Friday, January 30, 2009 5:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #647413
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse