SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
rg-722805
rg-722805
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 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. Smile

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)
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71006 Visits: 14949
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
rg-722805
rg-722805
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 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
rg-722805
rg-722805
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340647 Visits: 42644
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
rg-722805
rg-722805
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340647 Visits: 42644
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jluis_hdezg
jluis_hdezg
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340647 Visits: 42644
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search