0 Replies Latest reply: Oct 11, 2012 9:31 AM by 967544 RSS

    ODP.NET driver - Tuning

      ODP.NET driver - ODAC version (
      Application - Windows 2008 Standard SP 2
      DB - Oracle Database version 9i on Windows Server 2003

      I am using the version ( ODP.NET drivers in a .NET 4.0 Windows Forms test application built using Visual Studio 2010. The test application simply executes any SQL select statement passed to it and displays the row count. I am testing the drivers against a table containing several VARCHAR2 columns (varying length 10 to 50 maximum), a DATE column and a LONG RAW column with just TWO rows of data. Using the ODP.NET driver it takes around 30 seconds to retrieve ONE row which contains a 35MB excel file, while it takes only ~8 seconds using another driver from a leading company. Any suggestions for tuning the following code:

      Public Function TestGetTableFetch(ByVal strSQLStatement As String, ByVal strConnection As String) As DataSet
      'strConnection="DATA SOURCE=TestServer/TestDB;USER ID=DBO;Password=xyz"
      'strSQLStatement = "SELECT * FROM TESTTABLE WHERE FILE_ID='1'"

      Dim dstReturn As New DataSet
      Dim dbFact As System.Data.Common.DbProviderFactory
      Dim dtbDataTable As New DataTable

      dbFact = System.Data.Common.DbProviderFactories.GetFactory("Oracle.DataAccess.Client")

      If Not dbFact Is Nothing Then
      Dim DbConn As System.Data.Common.DbConnection = dbFact.CreateConnection()
      DbConn.ConnectionString = strConnection

      Dim DbCmd As System.Data.Common.DbCommand = DbConn.CreateCommand
      Dim dbDAD As System.Data.Common.DbDataAdapter = dbFact.CreateDataAdapter

      dbDAD.SelectCommand = DbCmd
      DbCmd.CommandText = strSQLStatement

      'Default=0=retrieve all; PK is always passed in
      'CType(DbCmd, Object).InitialLONGFetchSize = Integer.MaxValue

      Dim dblMILS, dblSeconds As Double
      Dim dtmS As DateTime = DateTime.Now

      Debug.WriteLine("ODP.Net FCH Start: " & DateTime.Now.ToString("HH:mm:ss.ffff"))
      Debug.WriteLine("ODP.Net FCH End : " & DateTime.Now.ToString("HH:mm:ss.ffff"))

      Dim dtmE As DateTime = DateTime.Now
      dblMILS = dtmE.Subtract(dtmS).TotalMilliseconds.ToString
      dblSeconds = (dblMILS * 0.001)

      Debug.WriteLine("ODP.Net FCH MS:" & dblMILS.ToString)
      Debug.WriteLine("ODP.Net FCH S:" & dblSeconds.ToString)

      End If
      Return dstReturn

      Catch ex As Exception
      End Try
      End Function