Queries vs statements vs prepared statements (performance q)

indianiec · **Joined:** Tue Jan 15, 2008 7:57 am **Posts:** 2

All,

I read fair amount on that topic, but still am not sure what wouls be the best choice for my system. The system is a fairly big web application on Tomcat 5 using Oracle 9 database. The most important thing is it must serve hundreds of requests per minute. Performance over all the other aspects.

The same application is also admin tool (but it uses separate server, so the only common thing for both of them is Oracle DB), and it needs to generate reports too. There is a reporting screen which lets you see all customer usage and all purchases. You can than filter all data to your needs (by date, by type etc.). On the same page there is pagination too.

Before someone says I should use separate reporting database - I cannot, the requirement is to use most up-to-date data when reporting (unless there is an efficient way to replicate DB stressing it less than other solutions do).

Code serving database looks like that:

Code:

private void constructFilteredCustomerUsageQuery(ReportSearchCommand cmd) {
      final Integer adjustedIndex = new Integer(
            cmd.getPageIndex().intValue() - 1);

      Session session = ((HibernateEntityManager) entityManager).getSession();
      DateFormat formatter = new SimpleDateFormat(cmd.getDateFormat());
      Criteria criteria = session.createCriteria(CustomerUsage.class);
      try {
         if (cmd.getExpiryDateFrom() != null
               && !cmd.getUsageDateFrom().equals("")) {
            criteria.add(Restrictions.gt("dateTime", formatter.parse(cmd
                  .getUsageDateFrom())));
         }
         if (cmd.getUsageDateTo() != null
               && !cmd.getUsageDateTo().equals("")) {
            criteria.add(Restrictions.lt("dateTime", formatter.parse(cmd
                  .getUsageDateTo())));
         }
      } catch (ParseException e) {
         log.error("Error converting dates while filtering", e);
      }
      if (cmd.getUsageType() != null && cmd.getUsageType() != -1) {
         UsageType[] usageTypeList = UsageType.values();
         for (UsageType usageType : usageTypeList) {
            if (usageType.ordinal() == cmd.getUsageType()) {
               criteria.add(Restrictions.eq("usageType", usageType));
               break;
            }
         }
      }
      if (cmd.getPackageId() != null && !cmd.getPackageId().equals("")) {
         criteria.add(Restrictions.eq("pkg.id", new Integer(cmd
               .getPackageId()).intValue()));
      }
      if (cmd.getPackageItemId() != null
            && !cmd.getPackageItemId().equals("")) {
         criteria.add(Restrictions.eq("pkgItem.id", new Integer(cmd
               .getPackageItemId()).intValue()));
      }
      if (cmd.getCountryId() != null && cmd.getCountryId() != -1) {
         criteria.add(Restrictions.eq("country.id", cmd.getCountryId()));
      }

      criteria.setFirstResult(adjustedIndex * cmd.getPageSize());
      criteria.setMaxResults(cmd.getPageSize() + 1);
      criteria.addOrder(Order.desc("dateTime"));

      // Find results
      List<CustomerUsage> customerUsagelist = criteria.list();
      cmd.setCustomerUsageList(customerUsagelist);

      if (cmd.getTotalPageNum() == null) {
         // get number of all pages
         Integer rowCount = (Integer) criteria.setProjection(
               Projections.rowCount()).uniqueResult();
         if(rowCount==null){
            int numAllPages = 1;
            cmd.setTotalPageNum(numAllPages);
         }else{
            int numAllPages = ((rowCount-1) / cmd.getPageSize().intValue())+1;
            cmd.setTotalPageNum(numAllPages);
         }
      }
   }

Now my question... is what I do a good solution? Should I maybe use prepared statements or Oracle stored procedures, or maybe Java stored procedures? It's not the question of code usability or anything. I need to know ONLY which way is the fastest and least database stressing.

Regards
Michal

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

-The gain from using pre-compiled queries is small; besides, it is likely that a reporting application will build the query as you go.
-If you use stored procedures, the database doesn't have to calculate the execution path every time, but your resultsets will still have to be mapped through hibernate objects.
-If your application does reporting exclusively, you can declare your mappings as read-only, that allows Hibernate to do some small optimizations.
-For queries and joins that are used very often, ask your DBA to build you "materialized views" (they are even faster than views because they contain actual data). Then, map your "report" entities to those views instead of to tables.