October 30, 2015 at 8:58 am
The query links a SQL Server table to a SQL Server Linked Server (Oracle) table across a very slow VPN.
A simple Select query against Oracle to return a few columns and one record is painfully slow.
The SQL Server (local to building) is very fast.
Objective: make as few calls to the Oracle Shared Server as possible.
Often, there are complex comparisons between a SQLServer field and the Oracle field due to data types or business rules.
The data is processed with some formula then the results are presented in the AS clause.
Question: Assume the inner Select calls both SQL Server and the SQL Server Linked Server from Oracle.
Would this make only 1 painfully slow call to Oracle?
Or, does the outer Select force a 2nd call?
Please keep in mind that the physical call across the VPN to Oracle is painfully slow. The rest of the processing time is tiny in comparison.
A Nested Statement is used to make the first calculation. The outer Statement might use multiple calculated results in the nested Where clause to make a final calculation.
In this example, the calculation is just a very simple text append.
Question: The outer
SELECT [ID_APD_Stips1], [Stip_Abv], VRSNavID, STIP_WasaMatter +'You' as Stip_WasaMatter_you
FROM
(SELECT
[ID_APD_Stips1], [Stip_Abv], VRSNavID, STIP_WasaMatter = [Stip_Name]+'WazaMatter'
FROM [RegulatoryDB].[dbo].[APD_Stips1]) INNER JOIN vsrNavigatorSHLBHL ON NV_RegToNavLink.NAV_Well_ID = vsrNavigatorSHLBHL.WELL_ID
as FinalOutput
October 30, 2015 at 9:05 am
Have you looked at the execution plan? Please post it here if you're having difficulty interpreting it.
John
October 30, 2015 at 10:12 am
Thanks, I will take you up on that!
We are just finishing it this morning and our office closes at noon today.
I will be back on Monday:
Just attached both Execution Plans.
In our preliminary design - the first working query includes
- A query that calls SQL Server Table and Oracle (SQL Server Remote Server over VPN)
This query evaluates field by Field and returns a 0 (matches) or 1 (not matched) in an As statement
- The 2nd Query only references the query above
- It takes all of the Fields returned values (0, 1) and used the OR to determine that all fields match
Basically, this drives a list box of Primary Key ID with an entity name..
The User knows that something on this list doesn't match between SQL Server and Oracle.
Upon selecting a single item in the list box, the interface runs the one recordset - exposing the details on the form.
A simple Oracle Select * from (Remote Server Table Name Where xxx = yyy) takes about 3 seconds to respond with one single record.
This query for about 1,000 records later than 1/1/2015 takes 11 seconds.
Our dedicated local SQL Server running on our local network returning the same query is miliseconds.
October 30, 2015 at 10:24 am
Mile Higher Than Sea Level (10/30/2015)
The query links a SQL Server table to a SQL Server Linked Server (Oracle) table across a very slow VPN.A simple Select query against Oracle to return a few columns and one record is painfully slow.
The SQL Server (local to building) is very fast.
...
Joining between local and remote with no WHERE clause is going to force SQL Server to pull all rows from the remote to evaluate the matches. If you are only returning one or a few rows from the remote server, try remotely executing a query using IN with your key list, running the result set into your local SQL Server instead. I've found OPENQUERY (and EXECUTE AT...) quite good for this.
Construct your string with your keylist: SELECT columnlist FROM vsrNavigatorSHLBHL WHERE WellID IN (MyList) then send it to the remote server.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 30, 2015 at 10:45 am
When executing a distributed select between two SQL Server instances, especially of the same version, the optimizer has some smarts about whether joins are performed locally or remotely for best results. There are also some hints to force specific behavior, if you know exactly what needs to be done or experiment enough with it. However, when running a distributed select between SQL Server and Oracle, these tuning techniques don't come into play.
Probably best option is to EXEC('SELECT...') AT LINKEDSERVER to get remote Oracle row set into a local temp table, and then join that with your SQL Server local table(s).
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 30, 2015 at 1:04 pm
Why do you even need a nested statement?
SELECT [ID_APD_Stips1], [Stip_Abv], VRSNavID, T.STIP_WasaMatter +'You' as Stip_WasaMatter_you
FROM [RegulatoryDB].[dbo].[APD_Stips1]
INNER JOIN vsrNavigatorSHLBHL ON NV_RegToNavLink(???).NAV_Well_ID = vsrNavigatorSHLBHL.WELL_ID
CROSS APPLY (SELECT STIP_WasaMatter = [Stip_Name]+'WazaMatter') T
How many records are in local table?
How many records are in remote table?
How many records do you get after JOIN?
November 2, 2015 at 8:32 am
The total table count is for STIPS is under 1,000 It could grow to about 10,000 over 4 years
The output is a few hundred records. There are only about 10 fields in the table.
The primary ID used in STIPS is under 15,000. Not all of them have STIPS. Usually, if there is a STIP, there re several.
The suggestion above for nesting the Select query in an execute for calling Oracle is interesting.
The SQL Server Linked Server uses the Oracle Java based ODBC via Listener.
From the Linked Oracle Server, a read-only view representing an Oracle table is created in the SQL Server DB.
A very simple Select * from <Oracle Linked Table view> where Primary Key ID = X takes over 8 seconds to respond with the 1st record.
Then, the rest of the records appear very quickly.
Simple table joins go downhill quickly and can take 10+ seconds to respond.
I think the ODBC to Oracle is an overwhelming bottleneck.
Perhaps I should start a new thread detailing how the SQL Server Linked Server through the View to the database view is constructed.
I will continue with the Execute suggestion and the IN (instead of the Where) for now.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply