Please forgive any formatting issues, this is my first time posting to this forum, but I've got a problem I really cannot figure out and am hoping someone here can help me find a solution.
My current situation is that I have an extremely large recordset (2 million records or so) I'm trying to query out of an oracle database using .Net and the
Oracle.DataAccess.dll library (Ver. 220.127.116.11).
... In my procedure:
Dim conn As New OracleConnection()
... connect to DB, etc (this all works fine) ...
Dim strSQLQuery = (Query for large recordset)
Dim cmd = New OracleCommand(strSQLQuery, conn)
Using Reader As OracleDataReader = cmd.ExecuteReader()
Now, this procedure works fine for smaller queries, but for this large query, it seems to hang indefinitely (I've waited up to overnight and then finally quit the program) on the
cmd.ExecuteReader() line and doesn't even get into the lines of code within the using statement... It seems to not be doing anything at all.
At first, I thought maybe this was an issue with my query itself, but when I try and run it using SQL Navigator, it returns the recordset within less than 10 minutes...
Am I doing something wrong? Is this normal? Is there a setting I can set to make the
OracleDataReader not hang?
Any and all help will be appreciated!
For the 2 million record query, are you selecting non-scalar data types? For example, if you have set ODP.NET to pre-fetch the selected LOB data and the LOBs are very large and/or numerous, then it could take a long time. If you turn off pre-fetching (i.e. InitialLOBFetchSize=0), then ODP.NET should perform similar to what you see with other query tools. I can't speak to how SQL Navigator works, whether it pre-fetches or defers the fetch, but by default most Oracle data access drivers do not pre-fetch.
If that isn't the issue, can you share your SELECT statement and the data types it queries?
You may also want to upgrade to the latest ODP.NET version just to eliminate running into an already known issue.