4 Replies Latest reply: Nov 6, 2011 1:17 AM by 857527 RSS

    Named Query or Native Query

    857527
      Hello,

      I am using native query to search for results on my search page. A friend of mine just told me that I better use named query or the time to get my search results will be too slow.

      Does Named Query give you faster result than Native Query?

      Thanks in advance.

      Regards,

      Hemen
        • 1. Re: Named Query or Native Query
          René van Wijk
          ".. better use named query.." you probably mean a query written using the persistence framework language (such as HQL, JPA-QL)

          Note that when you use a persistence query language, the query must be parsed to a native SQL language - the advantage though
          is that the persistence framework takes care of creating objects for you. When using a native query, you have to provide some mapping
          (http://docs.jboss.org/hibernate/entitymanager/3.6/reference/en/html/query_native.html).

          The best thing to do is compare the queries when they are executed on the database. See what execute plan
          the database creates (are there any full table scans in it that can optimized etcetera). It is not really possible
          to answer the question directly if the persistence framework query outperforms the native query or vice versa.
          This strongly depends on your fetch plan and fetching strategies you applied to particular entities that are used.
          To put it in other words, the if and how associated objects should loaded when the owning object is loaded.

          A goal would be to minimize the number of SQL statements and to simplify the SQL statements
          - http://docs.jboss.org/hibernate/core/3.3/reference/en/html/performance.html (shows an example for Hibernate but there are probably also docs related to JPA)
          • 2. Re: Named Query or Native Query
            857527
            Hello Rene,

            Thank you for your reply. I found it very helpful.

            By native query, I meant using the native sql like the following:

            String sqlQuery = "select * from tbl_spaceship where owner = ?";
            Query q = entityManager.createNativeQuery(sqlQuery, SpaceShip.class);
            q.setParameter( 1, "Han" );
            q.getResultList();

            (The above code is copied and pasted from the link you sent me http://docs.jboss.org/hibernate/entitymanager/3.6/reference/en/html/query_native.html )

            I have the sql query and get the result set just like the above.

            One question - on the above link it says:

            10.3. Named queries

            "Native named queries share the same calling API than JP-QL named queries. Your code doesn't need to know the difference between the two. "

            Does that mean that it has the same performance?

            Thanks

            Regards,

            Hemen
            • 3. Re: Named Query or Native Query
              René van Wijk
              When you define named queries (note that this is a Hibernate example) you can put them in for example XML,
              <query name="findItemsByDescription">
              <![CDATA[from Item item where item.description like :desc]]>
              </query>
              or a SQL query like
              <sql-query name="findItemsByDescription">
              <return alias="item" class="Item"/>
              <![CDATA[select {item.*} from item where description like :desc]]>
              </sql-query>
              You can call these queries by using the same code, for example,
              session.getNamedQuery("findItemsByDescription").setString("desc", description);
              This is what is being meant by sharing the same calling API.

              When taking about performance. You have to know something about what goes on under the hood.
              You have probably programmed something using straightforward JDBC, so you know how to queries
              get passed to the driver and send to the database. When using HQL or JPA-QL the queries first have
              to be parsed into an SQL language that the database can understand. In this case, we have an extra
              parsing step in between. Note that Native SQL queries, including stored procedure calls, the persistence
              framework still takes care of mapping the JDBC result sets to graphs of persistent objects.

              If you want to include a native SQL hint to instruct the database management systems query optimizer,
              for example, you need to write the SQL yourself. HQL and JPA-QL do not have keywords for this.

              The disadvantage of putting native SQL in your mapping metadata is lost database portability, because
              your mappings, and hence your application, will work only for a particular database. But usually this is of a
              minor concern as you are probably not creating a framework that has to work on every database.

              When you want to get behind the performance of your query, you really have to consult the database
              and look at the execution plan - A DBA can tell you exactly what is good and what can be optimized.
              • 4. Re: Named Query or Native Query
                857527
                Hello Rene,

                They say it's better late than never. Thank you so much for your explanation. I found it very helpful.

                Best regards,

                Hemen