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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » OPENROWSET select statement performance --...

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

Rate Topic

Display Mode

Topic Options

Author

Message

rg-722805

Posted Friday, August 29, 2008 3:16 PM

SSC 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

Jack Corbett

Posted Friday, August 29, 2008 5:09 PM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

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

rg-722805

Posted Saturday, August 30, 2008 2:16 PM

SSC 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

rg-722805

Posted Saturday, August 30, 2008 2:24 PM

SSC 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

Jeff Moden

Posted Saturday, August 30, 2008 4:11 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893, Visits: 26,770

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #561674

rg-722805

Posted Monday, September 08, 2008 10:59 AM

SSC 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

Jeff Moden

Posted Monday, September 08, 2008 7:11 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893, Visits: 26,770

rg (9/8/2008)

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

Post #565853

jluis_hdezg

Posted Friday, January 30, 2009 3:28 PM

Forum 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

Jeff Moden

Posted Friday, January 30, 2009 5:53 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893, Visits: 26,770

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

Post #647413

« Prev Topic | Next Topic »

Permissions