November 4, 2013 at 3:21 pm
Please look over and comment. This is new territory for me on SQL Server Linked Servers
The Problem: SQL Server's Linked Server (to Oracle) is on a company VPN across the country. The SQL View XFiles takes about 2.5 minutes to select 400K records. The With clause on the Linked Server Oracle server still take over 2 minutes.
Once the 400K records are local on SQL Server, a select query runs in about 1 second. The VPN is slow. The Oracle Data is a Read-Only view.
The idea is: Pull data once, reuse data hundreds of times.
The Front-end application allows a user to use queries with the Oracle data joined to live SQL Server Data for Quality Assurance reports.
Background: Users enter data in a Corporate Oracle Web application. Then users re-enter much of the same data into SQL Server application.
Something like this would be on a Job to run at Midnight and again at Lunch Hour each workday.
Question: would this work, is there a suggestion on how to make it better?
USE [RegulatoryDB]
GO
/****** Object: StoredProcedure [dbo].[sp_RefreshNav2] Script Date: 11/04/2013 10:28:34 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
Alter PROCEDURE [dbo].[sp_LinkedServer-MakeTableA_Nav2]
AS
IF OBJECT_ID('A_NAV2') IS NOT NULL DROP TABLE; --Results
GO
USE RegulatoryDB
GO
----Create new copy of Linked Server's table and insert data into table using SELECT INSERT
SELECT *
INTO A_NAV2
FROM XFiles -- XFiles is a View of the Oracle Linked Server - Table Nav2
The problem with creating a Temp Table. The Oracle Administrator may add new fields at any time. It is out of my control.
November 5, 2013 at 6:55 am
Mile Higher Than Sea Level (11/4/2013)
Please look over and comment. This is new territory for me on SQL Server Linked ServersThe Problem: SQL Server's Linked Server (to Oracle) is on a company VPN across the country. The SQL View XFiles takes about 2.5 minutes to select 400K records. The With clause on the Linked Server Oracle server still take over 2 minutes.
Once the 400K records are local on SQL Server, a select query runs in about 1 second. The VPN is slow. The Oracle Data is a Read-Only view.
The idea is: Pull data once, reuse data hundreds of times.
The Front-end application allows a user to use queries with the Oracle data joined to live SQL Server Data for Quality Assurance reports.
Background: Users enter data in a Corporate Oracle Web application. Then users re-enter much of the same data into SQL Server application.
Something like this would be on a Job to run at Midnight and again at Lunch Hour each workday.
Question: would this work, is there a suggestion on how to make it better?
USE [RegulatoryDB]
GO
/****** Object: StoredProcedure [dbo].[sp_RefreshNav2] Script Date: 11/04/2013 10:28:34 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
Alter PROCEDURE [dbo].[sp_LinkedServer-MakeTableA_Nav2]
AS
IF OBJECT_ID('A_NAV2') IS NOT NULL DROP TABLE; --Results
GO
USE RegulatoryDB
GO
----Create new copy of Linked Server's table and insert data into table using SELECT INSERT
SELECT *
INTO A_NAV2
FROM XFiles -- XFiles is a View of the Oracle Linked Server - Table Nav2
The problem with creating a Temp Table. The Oracle Administrator may add new fields at any time. It is out of my control.
If you want the procedure to rebuild and populate the a_nav2 table, this would do the whole thing.
ALTER PROCEDURE [dbo].[sp_LinkedServer-MakeTableA_Nav2]
AS
BEGIN
IF OBJECT_ID('dbo.A_NAV2', 'U') IS NOT NULL DROP TABLE dbo.A_NAV2;
SELECT * INTO dbo.A_NAV2
FROM dbo.XFiles;
END;
Ordinarily, I don't like SELECT *, but if the columns are going to change without you knowing about it and you need everything, then it's applicable here. You may have to do some character conversion in the view to get the string data to come across properly, but that will reveal itself when you do a simple SELECT from the view.
I think the real questions will revolve around how your reporting application works. If it's dynamic and uses the fields in the A_NAV2 table to determine what the users can query and filter by, then it'll technically work. If not, then your application will only handle the fields you knew about when you wrote it. If the Oracle DBA can add fields without telling you, then it's also possible that they could delete fields without telling you, which would break the application. Either way, it's best if your application is dynamic and uses the table structure that's in place when it runs. This will be more complicated to write up-front, but will keep you out of a maintenance nightmare that comes along at the Oracle DBA's whim.
The other issue for your destination table is performance. Presumably, you're going to need some indexing on your table if you have any heft to the number of rows. Hopefully, there are some non-changing columns in the data from Oracle that will benefit your application's performance. If the users can query any combination of fields and filter it any way they want, Gail has an article on catch-all queries that you might find helpful at http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/.
November 5, 2013 at 8:55 am
Thank you so very much. This is all very valuable to me.
At least in the past, only new field have been added to the Oracle views.
My 1st role is application developer followed by DB admin. My plan is to build plenty of error trapping that will exit gracefully and notify me if a user has encountered structural changes.
The indexing was next on my ToDo list. Lucky for me, the Oracle side marks record as deleted and don't actually delete the records themselves.
The first field is basically an autocounter assigned number. They tell me this primary-key is assigned for lifetime including the archive.
Thanks for the link and suggestion.
November 5, 2013 at 12:24 pm
Mile Higher Than Sea Level (11/5/2013)
Thank you so very much. This is all very valuable to me.At least in the past, only new field have been added to the Oracle views.
My 1st role is application developer followed by DB admin. My plan is to build plenty of error trapping that will exit gracefully and notify me if a user has encountered structural changes.
The indexing was next on my ToDo list. Lucky for me, the Oracle side marks record as deleted and don't actually delete the records themselves.
The first field is basically an autocounter assigned number. They tell me this primary-key is assigned for lifetime including the archive.
Thanks for the link and suggestion.
Glad I was able to help. Good luck with your application.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply