ODP.NET driver - ODAC version 18.104.22.168.2 (22.214.171.124)
Application - Windows 2008 Standard SP 2
DB - Oracle Database version 9i on Windows Server 2003
I am using the version 126.96.36.199.2 (188.8.131.52) 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