Post reply

Select on Oracle linked server table.

  • February 23, 2010 at 5:10 am

    #156708

    Dear Experts,

    I have added a oracle linked server in sql server 2005.

    I need to access a view in this linked server, on which I dont have any control.

    (Apart from "Select" rights). Total there are 1.3 million records in that view.

    I need to execute the below query for a repot.

    "Select * From View Where state_code=5"

    This query returns hardly 5000 records, but it takes almost 1 hour.I think there's no index created on the field "state_code" in oracle view,

    and I dont have rights to create it too.

    Is there any way to make it faster when I execute the query from my SQL Server 2005 ?

    (Though I have put a request to the client for adding that index,

    I would like to know if there is any other way out).

    Thanks in advance

  • February 23, 2010 at 5:38 am

    #1123547

    The problem may be that all 1.3 million rows are being sent to the MSSQL instance before the filter is applied.

    This could take a long time on a slow link.

    Try:

    1. using OPENQUERY instead of the linked server syntax.

    2. restricting the number of columns.

  • February 23, 2010 at 2:44 pm

    #1123904

    i believe i saw thread which stated that openquery against a linked server was faster for a linked server vs a query on your server using 4 partnaming 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.

  • February 23, 2010 at 9:13 pm

    #1124052

    Thanks you all.

    Inorder to use OPENQUERY option, do we need to create the linked server using MSDAORA provider.?

    Right now we are using OLEDB provider.

    Thanks.

  • February 24, 2010 at 11:06 am

    #1124408

    I think yes. MSDAORA is the name of the Microsoft OLE DB Provider for Oracle-->http://www.sqlmag.com/Articles/ArticleID/49687/49687.html?Ad=1

    MJ

Viewing 5 posts - 1 through 4 (of 4 total)

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