Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Unanswered: sql server linked to oracle table...Very Slow

    Hi i have a sql server instance on my system and it is linking into an oracle database on another server. When i run queries against this other server...it takes forever...

    However, when i use access, and link the table and run the same query against the oracle database...it runs immediatly.

    I am very confused as to why there would be such a performance difference and why sql server would run so slow.

    I am wondering if it has something to do with the way i configured the linked server. there are several options that I didn't know what they meant.

    collation compatible (not selected)
    Data access (selected)
    RPC (not selected)
    RPC Out (not selected)
    collation name
    connection timeout
    query timeout

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How did you link it?

    How did you connect in Access?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Location
    Chicago
    Posts
    5

    Re: sql server linked to oracle table...Very Slow

    Try openquery function for the queries that are being queried on the Oracle linked server and see if there is any improvement.

    Even, I had this problem


    Originally posted by srahan
    Hi i have a sql server instance on my system and it is linking into an oracle database on another server. When i run queries against this other server...it takes forever...

    However, when i use access, and link the table and run the same query against the oracle database...it runs immediatly.

    I am very confused as to why there would be such a performance difference and why sql server would run so slow.

    I am wondering if it has something to do with the way i configured the linked server. there are several options that I didn't know what they meant.

    collation compatible (not selected)
    Data access (selected)
    RPC (not selected)
    RPC Out (not selected)
    collation name
    connection timeout
    query timeout

  4. #4
    Join Date
    Nov 2003
    Posts
    14

    sandman

    Well I linked it in SQL Server using the Microsoft OLE DB Provider for Oracle. In access, i used ODBC.

    In SQL Server the closest option I have to using ODBC is to use the Microsoft OLE DB Provider for ODBC...i tried that it didnt solve anything.

    I checked all my registry settings to make sure that they are updated. I am using the Oracle 9i client...i wanted to use Oracle 8i as that is what the server is and that is what most of the references I found used...however, my system is running on XP Pro and Oracle 8i simply didn't install.

    I am still very stuck and not really moving anywhere. THe queries are taking over 10 minutes, while the equivalent queries take several seconds in access through ODBC...there is no point of me using SQL Server if its gonnna be so slow. I doubt this is just the way it works because I can't imagine anyone getting much use out of a system this slow.

    Well thanks for any help you can offer.

  5. #5
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    i havent used oracle with sql in a while but i think if you install the oracle client tools on the server that has sql on it, you will then have a more native oracle driver at your disposal...good luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •