1 2 Previous Next 25 Replies Latest reply: Jan 6, 2012 10:09 AM by gimbal2 RSS

    JDBC slower than TOAD?

    548296

      Hi,

      I run the following SQL in TOAD and it takes 17 min. In JDBC it takes 3 hours!

      Please advise.

      SELECT /*+ parallel (i,8) */ i.ap_id
      FROM table1 i
      WHERE i.main_ind = '1'
      AND i.state = 'AS'
      AND 1 > (SELECT /*+ parallel (ii,8) */ COUNT ( * )
      FROM table1 ii
      WHERE i.ap_id = ii.ap_id
      AND ii.end_date = TO_DATE ('20280119051407', 'YYYYMMDDHH24MISS'));

        • 1. Re: JDBC slower than TOAD?
          Joe Weinstein-Oracle
          If you parameterize the SQL in JDBC, that provides a possible reason,
          but if you claim that a plain SQL string is sent to the DBMS, and
          takes longer to execute from one type of client than another, that's
          more tricky...

          Can you normalize your two cases to establish that the connection
          is a fresh, and otherwise unused? (I don't want the DBMS and/or
          session primed in any way for either case). Can you do a few cycles
          test jdbc, test toad, test jdbc, test toad... ?

          Edited by: Joe Weinstein on Dec 22, 2011 12:28 PM
          • 2. Re: JDBC slower than TOAD?
            548296
            Hi,

            The SQL is NOT parameterized. I run it as it is.

            I do run it on the connection that was used before for other queries.
            • 3. Re: JDBC slower than TOAD?
              Joe Weinstein-Oracle
              then the only obvious factor is the priming of the DBMS and/or session
              with the query plan(s) and/or cached data that would make it faster
              than if the same query is done with a brand-new connection. It would
              be interesting to hear the results of a repeating, alternating test as
              I described.
              • 4. Re: JDBC slower than TOAD?
                548296
                In Toad I run the query first after I opened the Toad - still running quickly.

                In JDBC I added -DdefaultRowPrefetch=30000 JVM arg and it reduced the time to 2 hours, but still its 2 hours vs 17 min in Toad.

                The table is very big: ~90M records.

                BTW I checked in Precise and found no heavy queries during run period. So maybe it's not the query itself, but the way JDBC is handling query results?
                The query brings 300K records. But it Toad it also brought me all records.

                Your help will be welcomed.

                Edited by: Benzion on Dec 23, 2011 9:27 AM
                • 5. Re: JDBC slower than TOAD?
                  gimbal2
                  Benzion wrote:
                  In Toad I run the query first after I opened the Toad - still running quickly.

                  In JDBC I added -DdefaultRowPrefetch=30000 JVM arg and it reduced the time to 2 hours, but still its 2 hours vs 17 min in Toad.

                  The table is very big: ~90M records.

                  BTW I checked in Precise and found no heavy queries during run period. So maybe it's not the query itself, but the way JDBC is handling query results?
                  The query brings 300K records. But it Toad it also brought me all records.

                  Your help will be welcomed.

                  Edited by: Benzion on Dec 23, 2011 9:27 AM
                  "JDBC" doesn't really do anything, it is only a specification. If you want to blame something you'll have to poke a finger at the specific database driver you are using.
                  • 6. Re: JDBC slower than TOAD?
                    548296
                    ojdbc6.jar

                    Manifest:
                    Manifest-Version: 1.0
                    Implementation-Vendor: Oracle Corporation
                    Implementation-Title: ojdbc6.jar
                    Implementation-Version: Oracle JDBC Driver version - "11.1.0.6.0-Produ
                    ction+"
                    Implementation-Time: Tue Oct 30 03:33:58 2007
                    Specification-Vendor: Oracle Corporation
                    Sealed: true
                    Created-By: 1.6.0 (Sun Microsystems Inc.)
                    Specification-Title: Oracle JDBC driver classes for use with JDK6
                    Specification-Version: Oracle JDBC Driver version - "11.1.0.6.0-Produc
                    tion+"
                    Main-Class: oracle.jdbc.OracleDriver

                    Name: oracle/sql/converter_xcharset/
                    Sealed: false

                    Name: oracle/sql/
                    Sealed: false

                    Name: oracle/sql/converter/
                    Sealed: false


                    I also found that actual Statement.executeQuery() takes 10 sec. All the rest of the time is ResultSet.next()
                    • 7. Re: JDBC slower than TOAD?
                      gimbal2
                      Benzion wrote:
                      I also found that actual Statement.executeQuery() takes 10 sec. All the rest of the time is ResultSet.next()
                      That makes sense (that most time is spent scrolling through the resultset I mean).

                      What is your network setup? Is this database running on your local machine? Same network? Different network? Different building? Over the internet? VPN?

                      Are you running Toad and the Java app on the same machine?

                      Are you connecting using the same user in toad and Java?
                      • 8. Re: JDBC slower than TOAD?
                        Joe Weinstein-Oracle
                        What are you doing in JDBC with the resultset rows? And for TOAD? Show the code
                        in both cases. It may be the way you request data in JDBC requiring the driver to
                        convert the data to complicated objects or convert characters, whereas (guessing)
                        in TOAD, you just print it out in whatever way it sees fit? What happens in JDBC if you
                        just do your loop of rs.next() calls, without explicitly accessing any row data? That test
                        may parse out the problem between raw data delivery and internal driver processing.
                        • 9. Re: JDBC slower than TOAD?
                          796440
                          Benzion wrote:
                          The query brings 300K records. But it Toad it also brought me all records.
                          This sounds suspicious. Are you really bringing all 300k records back to the client in Toad? Is Toad indicating unambiguously that IT has pulled down 300k records, or did you actually scroll through all 300k records?

                          Or is it just that Toad is telling you that there are 300k records?

                          Usually with tools like Toad, it only brings back a small number of records--tens or hundreds--and doesn't retrieve any more from the server until you display more. So if you're comparing Toad saying "query is done, here is a page of data" against Java code that explicitly calls rs.next() 300k times, then, yes, I would very much expect the Java code to be slower because it's doing a lot more.
                          • 10. Re: JDBC slower than TOAD?
                            jschellSomeoneStoleMyAlias
                            Benzion wrote:
                            Hi,

                            I run the following SQL in TOAD and it takes 17 min. ...Please advise.
                            If it was me I would start with the concern that I have any query at all that takes 17 minutes.
                            • 11. Re: JDBC slower than TOAD?
                              rp0428
                              Please post the Java code you are using. You posted the query but the query has NOTHING to do with the client side of things.

                              Client side performance issues for query result sets are generally due to one of two things:

                              1. You are not bringing the query results back to the client efficiently

                              Most standard JDBC use a default fetch size of 10. When you use rs.next() the first time a round trip is made to the database, 10 rows are returned to the client and the driver gives you access to the first row. Each NEXt() call that you make will return one more row from the first 10 unless you have already gotten 10 in which case another trip to the database is made to get 10 more.

                              You can set the fetch size at the connection, resultset or statement level to a higher value:
                              statement.setFetchSize(1000); 
                              This will reduce the number of round trips to the database and can greatly improve performance.

                              2. You are not processing results being brought back efficientlly.

                              You should use Oracle's driver functionality whenever possible. Many people do not seem to know, for some reason, that the Oracle drivers provide dramatic functionality and performance enhancements over the standard JDBC drivers.

                              But in order to use that functionality you have to use the Oracle version of objects:

                              1. Use OracleConnection instead of Connection
                              2. Use OracleStatement instead of Statement
                              3. Use OracleResultSet instead of ResultSet (VERY IMPORTANT!)
                              4. ... and all the other objects

                              The use of OracleResultSet gives tremendous performance advantages over the standard object. When Oracle fetches data for an OracleResultSet IT DOES NOT CONVERT THE DATA TO JAVA DATATYPES UNTIL YOU GET THE DATA!

                              The Oracle driver retrieves all data from Oracle in the native (highly compressed) Oracle format. It stores this data UNCHANGED in an OracleResultSet. But if you use a standard ResultSet instance the driver will convert ALL of the data from native Oracle to Java data types whether you ever access any of the data.

                              Then when you do access the data, using GETxxx, only the data that you access is converted. So if a row has 100 columns and you only access 5 of them only 5 datums get converted; contrast this will converted all 100 columns for all rows using the standard object.

                              I would suggest two things. Change your code to use Oracle object instances and set the fetch size to a higher value.
                              You can easily do some performance comparisons to see the results and they are amazing.
                              • 12. Re: JDBC slower than TOAD?
                                EJP
                                for some reason
                                For the reason that it isn't documented in the Javadoc?
                                over the standard JDBC drivers
                                There are no 'standard JDBC drivers'. There is an API, provided with Java, and an SPI, provided by the vendor. OracleStatement is Oracle's implementation of the Statement interface; ditto OracleResultSet and ResultSet, etc. Are you suggesting that Oracle has provided two sets of implementation classes for some bizarre reason? Otherwise your post doesn't make any sense. You can't just 'use a standard ResultSet instance' for example: you have to use whatever implementation the SPI returns to you.
                                • 13. Re: JDBC slower than TOAD?
                                  rp0428
                                  I think most people understood that I am talking about how to use Oracle Objects to improve performance, often substantially.

                                  But if you need clarification replace 'over the standard JDBC drivers' with 'over the standard JDBC objects', of which I specifically mention OracleConnection, OracleStatement and OracleResultSet in place of Connection, Statement and ResultSet.

                                  And you can indeed 'use a standard ResultSet instance' because java.sql.ResultSet IS the standard that all properly implemented SPIs will return to you. Technically java.sql.ResultSet is an interface so it cannot be instantiated directly but the returned value is an instance of it in a functional sense.

                                  And no, I am not suggesting that Oracle has provided two sets of implementation classes for some bizarre reason. They have implemented them for reasons that include those that I mentioned in my post including performance.

                                  If you request a java.sql.ResultSet from the Oracle JDBC driver you will have access to only the functionality documented for that interface in the Java API docs. If instead you cast the object to 'oracle.jdbc.OracleResultSet' you will have access to additional functionality including the lazy data transformation that I discussed. The Oracle JDBC Developer's Guide, http://docs.oracle.com/cd/E11882_01/java.112/e16548/toc.htm, has details on the extensions that Oracle provides.

                                  These Oracle extensions have been available for many years but, for some reason, even though they are clearly documented, many developers, including you it seems, do not know about them.

                                  Sorry you got confused.

                                  Now that I've cleared that up for you can we all get back to the original topic if the question hasn't been answered?
                                  • 14. Re: JDBC slower than TOAD?
                                    gimbal2
                                    These Oracle extensions have been available for many years but, for some reason, even though they are clearly documented, many developers, including you it seems, do not know about them.
                                    <lies removed>

                                    Yet even if I knew about the extensions, I wouldn't use them anyway unless I had no other choice. If things don't perform well with bog standard JDBC code, there is a flaw in either the code, the design or in the environment which needs to be corrected. There are reasons why you program against a well known API. Portability is the least interesting reason, the most interesting one I'll translate literally from Dutch: "A party of recognition". Standardized code, it is a thing of beauty. Recognizable, self-documenting, authorized.
                                    1 2 Previous Next