none
Oracle fill DataSet very slooooooow RRS feed

  • Question

  • Please see the following code, same sql script, runs in PL/SQL developer, it only costs less than 1 second to get the result.

    But in .Net, it will hang, never get a result.

    Any help will be greatly appreciated!

     

    Imports System.Data
    Imports System.Data.OracleClient

    Module Module1

        Sub Main()

            Dim conn As OracleConnection = New OracleConnection("Data Source=...;User Id=...;Password=...")
            conn.Open()
            Dim ds As New DataSet
            Dim comm As OracleCommand = New OracleCommand
            comm.Connection = conn
            comm.CommandText = "select * from (SELECT im.ReceiveTS, im.MsgType,  CASE im.IsOutbound WHEN 0 THEN 'PMS' WHEN 1 THEN 'CRS' END  ""From"", im.Status, im.BID, h.HotelName, im.SequenceID, im.IHConfID, im.PMSResNum, im.PMSSharedID, im.ResAction, im.ResultReceiveTS, im.fileName, im.ID,  0 Errors FROM Databridge.Msg im left join DataBridge.Hotels h  on h.bid=im.bid  WHERE im.PMSSharedID = '1001BI' order by id desc ) where rownum <100 order by receivets desc"
            comm.CommandType = CommandType.Text

            Dim adapter As OracleDataAdapter = New OracleDataAdapter(comm)
            adapter.Fill(ds)
            conn.Close()


        End Sub

    End Module

    Friday, April 6, 2007 6:04 PM

Answers

  • It's possible Oracle is configured to open a transaction for remote connections and that somehow that transaction is blocking the work you are doing. I am not too familiar with Oracle client programs, but I've run into this issue with DB2. You should check the logs on teh Oracle server to see if the connection is being made and at which point the client is hanging or waiting.

     

    Jeff Derstadt - MSFT

     

     

    Monday, April 9, 2007 5:04 PM

All replies

  • It's possible Oracle is configured to open a transaction for remote connections and that somehow that transaction is blocking the work you are doing. I am not too familiar with Oracle client programs, but I've run into this issue with DB2. You should check the logs on teh Oracle server to see if the connection is being made and at which point the client is hanging or waiting.

     

    Jeff Derstadt - MSFT

     

     

    Monday, April 9, 2007 5:04 PM
  •  

    It's possible Oracle is configured to open a transaction for remote connections and that somehow that transaction is blocking the work you are doing. I am not too familiar with Oracle client programs, but I've run into this issue with DB2. You should check the logs on teh Oracle server to see if the connection is being made and at which point the client is hanging or waiting.

     

    Jeff Derstadt - MSFT

    Monday, April 9, 2007 5:08 PM