This discussion is archived
0 Replies Latest reply: Oct 11, 2012 7:31 AM by 967544 RSS

ODP.NET driver - Tuning

967544 Newbie
Currently Being Moderated
ODP.NET driver - ODAC version 11.2.0.1.2 (4.112.1.2)
Application - Windows 2008 Standard SP 2
DB - Oracle Database version 9i on Windows Server 2003

I am using the version 11.2.0.1.2 (4.112.1.2) 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
Try
'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"))
dbDAD.Fill(dtbDataTable)
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)

dstReturn.Tables.Add(dtbDataTable)
End If
Return dstReturn

Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Function

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points