Post reply

Apply WHERE criteria to a view based on a linked server

  • August 9, 2010 at 2:37 am

    #225260

    Hi to all.

    I am facing the following issue:

    I have a SQL Server 2005 32-bit running on a 64-bit Windows Server.

    I have created a linked server to an oracle Database using Microsoft OLE DB Provider for Oracle.

    I created a view in the following manner:

    CREATE VIEW View1 AS SELECT Field1 , Field2, FieldX FROM

    OPENQUERY(OracleLinkedServer,'SELECT Field1 , Field2, FieldX FROM Table1');

    My problem is that although this works, it causes SQL Server first to salect all records from Oracle and then apply the where part of the statement. This has a huge impact on performance.

    To make things more complicated, the command comes from an ERP system so I cannot change the way the command is created and executed. The ERP system sends a command like: SELECT * FROM View1.

    I think I read somewhere in SQL help that this behavior depends on the driver. Is there anything I can do to correct this?

    Thanks in advance.

  • August 9, 2010 at 5:58 am

    #1204089

    For Openquery to filter the results on the Linked server side, I believe the where statement needs to be inside the openquery to avoid the performance issue where the table is copied locally to temp, and then the WHERE statement applied agaisnt it's results:

    There are several threads which stated that openquery against a linked server was faster for a linked server vs a query on your server using 4 part naming conventions;

    for example, select * from LinkedServer.Databasename.dbo.MillionRowTable where something=somethingelse

    vs

    SELECT *

    FROM OPENQUERY(LinkedServer, 'select * from MillionRowTable where something=somethingelse');

    the reason: the first query gets the million rows locally into temp, then does the filtering, where the openquery does the work on the linkedserver, and returns just the results.

    It looks like creating a view and then applying the WHERE statement emulates the way a normal linked server behaves.

    can you copy the data locally and refresh it at intervals? how close to NOW does the data fromt eh linked server really need to be? can it be a copy of yesterdays data instead of a linked server query?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 9, 2010 at 6:25 am

    #1204104

    Hi Lowell.

    Thanks for the reply.

    Actually it is used for the following scenario:

    In a manufacturing company, an Oracle database is used to communicate and control the manufacturing process. Each time an action is performed, it is recorded in the database. There is an ERP system on a SQL database that reads some tables and uses the data (for example quantity cosnumed and produced for machine X) to reflect these changes. The plant is running 24/7 and the data needs to be real time (as in updated every 15 minutes or so). I can use other ways to do this (create stored procedures on SQL to do the sync). Because I also need to update the rows back into Oracle, the time required to do this through SQL is much greater than though the ERP. The only issue I have is the one mentioned so I am trying to see if there is an alternative before starting to re-implement the logic in another way.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply