7 Replies Latest reply: Oct 8, 2013 3:02 PM by KWilcox RSS

    performance issue with ODP.NET DataReader

    864049
      Either I am doing something wrong or the performance is really bad. I tried to play with FetchSize property by increasing it to 1M, 4M and even 8M but that did not help. I test MS SQL 2008 and Oracle 11g R2 with identical data content. When using ADO/OLEDB DataSet I am getting similar performance for both dbms. When working with ADO.NET and DataReader from ODP.NET, the performance of reading data is 20-25 times worse. I just hope we are missing something in our code (a switch, parameter etc). I found somebody has has already touched this topic but there was no conclusion. Can anyone please help ? Any hint will be greatly appreciated. Thank you.
        • 1. Re: performance issue with ODP.NET DataReader
          852174
          I can only quote what I have read somewhere, but others have complained about performance with the beta version of ODP.NET.

          It seems that Oracle will focus on performance issues when it gets closer to the release version.
          • 2. Re: performance issue with ODP.NET DataReader
            Alex.Keh .Product.Manager-Oracle
            Can you post the ODP.NET code you are using? What ODP.NET version are you using?

            Alex
            • 3. Re: performance issue with ODP.NET DataReader
              865913
              I've got the same problem. This is my code:

              string connectionString = "User Id=*****; Password=******;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=******)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=********))); Pooling=false; Enlist=false";

              using (OracleConnection connection = new OracleConnection(connectionString))
              {
              connection.Open();

              string query = "SELECT xxxx, xxxxx FROM xxxxx WHERE TRUNC(xxxxx) = to_date('10-mag-2011')";
              using (OracleCommand command = new OracleCommand(query, connection))
              {
              using (OracleDataReader reader = command.ExecuteReader())
              {
              int i = 1;
              while (reader.Read())
              {
              Console.WriteLine(reader[0] + " " + i);
              i++;
              }
              }
              }
              }

              It takes 33 secords to retrieve 856 rows. The same query without WHERE clause starts immediatly retrieving data.
              What is the problem? I forgot something? Because in this way it's impossible to use it in production.

              I'm using the ODP.NET contained in ODAC 11.2 Release 3, in particular the version for Framework 4.

              Any help is appreciated!

              Thanks
              Paolo
              • 4. Re: performance issue with ODP.NET DataReader
                864049
                As per Alex request, her are some details. The version of ODP.NET is 11.2.0.2.1
                I do not believe this ODP is "beta" which user 849171 kindly mentioned as a potential reason of problems. We installed that from Oracle 11g R2.

                The code that runs far below any expectation is very similar to one in previous post (from Paolo):

                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                ..... connection.Open();
                ..... string sql = "SELECT * FROM table WHERE some_condition";
                ..... using (OracleCommand command = new OracleCommand(sql, connection))
                ..... {
                .......... using (OracleDataReader reader = command.ExecuteReader())
                .......... {
                .................... while (reader.Read())
                .................... {                                    
                .................... // ...do something very quick and simple
                .................... Console.WriteLine( reader.GetString(0) + ", " + reader.GetString(1) + ", " + reader.GetString(3));
                .................... }
                .......... }
                ..... }
                }

                When the recordset retrieved is larger (~40000 records) the code above runs just too slow (comparing to SQL.NET provider running against SQL Express). Initially we thought this is our Oracle database tuning issue or such. So we did tests with classic ADO/OLEDB provider and some unmanaged code and the same sequence of execution runs like a breeze. No single change was done to the database. So, it is not Oracle engine. The situation does not change if we start playing with FetchSize nor when we breaks the recordset into smaller chunks (i.e. 1000 recs each). I understand that there might be some advantage running SQL.NET over ODP, after all .NET is native to Microsoft, but the difference we see is surprisingly big.

                Thanks
                • 5. Re: performance issue with ODP.NET DataReader
                  Alex.Keh .Product.Manager-Oracle
                  I ran the test case with ODP.NET 11.2.0.2.1, OLEDB, and MS OracleClient, but did not see an order of magnitude performance difference among the providers. There was a performance difference, but ODP.NET was fastest.

                  Can you email me a complete VS project/solution test case? alex.keh (at) oracle.com. Please include your schema definition, though you don't have to include the actual data.

                  Previously, did you use an older ODP.NET version before and had better performance? Just wondering if there is some particular issue with this specific ODP.NET release.

                  Alex
                  • 6. Re: performance issue with ODP.NET DataReader
                    Alex.Keh .Product.Manager-Oracle
                    I haven't received a complete test case yet with schema definition. If anyone has one to share with me or has opened an SR on this issue, I can investigate this issue with the ODP.NET development team. My email is alex.keh (at) oracle.com.
                    • 7. Re: performance issue with ODP.NET DataReader
                      KWilcox

                      As written I would say it is a coding issue and related to accessing the data not ODP.NET.  Instead of "SELECT xxxx, xxxxx FROM xxxxx WHERE TRUNC(xxxxx) = to_date('10-may-2011')" you would be better off with something like this "SELECT xxxx, xxxxx FROM xxxxx WHERE xxxxx >= to_date('10-may-2011') and xxxxx < to_date('11-may-2011') ".  The reason is that you are applying the truncate function to every row of the table and bypassing any index on the date column (assuming you don't have a function based index on trunc(xxxxx).  (Note:  I changed mag to may in my example.)