ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: iBatis hangs query w/ multiple joins
Date Mon, 16 Jan 2006 13:35:49 GMT
In your JDBC example, you were not using a transaction.  The error you got
from SQL server was a transaction error.   You can fix this a number of
ways:

1) Increase the size of the transaction log.
2) Decrease the size of the query << My suggestion, as I usually find that
queries this big are unecessary)
3) Don't use transactions, which requires that you bypass the iBATIS
transaction manager by supplying your own connections.

Cheers,
Clinton


On 1/16/06, Jorge DeCastro <jorge.decastro@flightcentre.co.uk> wrote:
>
> Hi again,
>
> My problem described below was due to the fact that SQL Server on our dev
> environment grew too large. The SQL error thrown by iBatis after a long
> wait was:
>
> "The log file for database 'tempdb' is full. Back up the transaction log
> for the database to free up some log space."
>
> My question now is: if I ran the exact same query via straight JDBC or via
> the SQL Server client console, the query ran successful. Through iBatis, I
> could even get simpler queries to run, including the one below without the
> additional join to TAXEstimate.
> Is there any explanation why iBatis would hang on a query that executes
> via straight JDBC?
>
> Thanks
> j.
>
>
>
>
>
>
> I'm using iBatis to access a SQLServer db and I'm running into the
> following problem. If I run the code below via straight JDBC it runs
> perfectly.
>
> private static final String SQL_STATEMENT = "SELECT DISTINCT "+
>                 "TOP 10 " +
>                 " PubAir.PubAirID," +
>                 " PubAir.FromGeoAirPort," +
>                 " PubAir.ToGeoAirPort, " +
>                 " PubAirAgreement.ValidFromDate," +
>                 " PubAirAgreement.ValidToDate," +
>                 " PubAirPrice.ADTCostAmount," +
>                 " TAXEstimate.TaxAmount," +
>                 " TAXEstimate.CurrencyCode" +
>                 " FROM PubAirRoutingPrice" +
>                   " INNER JOIN PubAirPrice ON
> PubAirRoutingPrice.PubAirPriceID = PubAirPrice.PubAirPriceID" +
>                 " INNER JOIN PubAir INNER JOIN PubAirAgreement ON
> PubAir.PubAirAgreementID = PubAirAgreement.PubAirAgreementID" +
>                 " INNER JOIN PubAirRouting ON PubAir.PubAirID =
> PubAirRouting.PubAirID
> ON PubAirRoutingPrice.PubAirRoutingID = PubAirRouting.PubAirRoutingID" +
>                 " INNER JOIN TAXEstimate ON TAXEstimate.InboundFromGeoCode=
> dbo.PubAir.ToGeoAirPort" +
>                 " WHERE (PubAirPrice.ADTCostAmount > 0)" +
>                 " AND (PubAir.FromGeoAirPort LIKE ?)" +
>                 " AND (PubAir.ToGeoAirPort LIKE ?)";
>
>         public List findFaresFromToAirportsWithinDatesViaStraightJDBC(
>                         String fromAirport,
>                         String toAirport,
>                         String airlineFilter,
>                         String type,
>                         Date startDate,
>                         Date endDate,
>                         Integer records) {
>
>                 logger.debug("Retrieving '" + records + "' fares from '" +
> fromAirport
>                                 + "' to '" + toAirport + "', start date '"
> + startDate
>                                 + "', end date '" + endDate + "'");
>
>                 DataSource ds = getDataSource();
>                 Connection c = null;
>                 PreparedStatement ps = null;
>                 ArrayList l = new ArrayList();
>                 try{
>                         c = ds.getConnection();
>                         ps = c.prepareStatement(SQL_STATEMENT);
>                         ps.setString(1, fromAirport + "%");
>                         ps.setString(2, toAirport + "%");
>                         ResultSet rs = ps.executeQuery();
>                         while (rs.next()){
>                                 Air a = new Air();
>                                 a.setFromAirport(rs.getString
> ("FromGeoAirPort"));
>                                 a.setToAirport(rs.getString
> ("ToGeoAirPort"));
>                                 a.getAirPrice().setAdultCost(
> rs.getBigDecimal("ADTCostAmount"));
>                                 a.getAirPrice().setTax(rs.getBigDecimal
> ("TaxAmount"));
>                                 l.add(a);
>                         }
>                 }catch(Exception e){
>                         log.debug("error handling jdbc connection", e);
>                 }finally{
>                         if (ps != null){
>                                 try{
>                                         ps.close();
>                                 }catch(Exception ignored){
>                                 }
>                         }
>                         if (c != null){
>                                 try{
>                                         c.close();
>                                 }catch(Exception ignored){
>                                 }
>                         }
>
>                 }
>                 logger.debug("Retrieved '" + l.size() + "' fares");
>                 return l;
>         }
>
>
> The same query running on the SQLServer client console works fine. Now if
> I run it through iBatis as I intend, the query hangs forever with no error
> reported on the logs.
> I'm buffled because it was my understandnig that iBatis was just doing the
> ResultSet wrapping and unwrapping for me, and nothing else fancy (like
> hibernate). Hence, it should simply execute a preparedStatement, populate
> the beans according to my settings, and return a list.
> Below are my mappings. Note that the "getCompanyCodes" query works just
> fine via iBatis, which seems to tell me iBatis is having problems with the
> joins.
>
> <cacheModel id="faresCache" type="LRU" readOnly="true">
>           <flushInterval hours="24"/>
>           <property name="size" value="1000" />
>         </cacheModel>
>
>         <typeAlias alias="airfare" type="com.flightcentre.model.Air" />
>
>     <resultMap id="getPubAirJoinAgreementJoinPrice" class="airfare">
>                 <result property="id" column="PubAirID"/>
>                 <result property="fromAirport" column="FromGeoAirPort"/>
>                 <result property="toAirport" column="ToGeoAirPort"/>
>                 <result property="airAgreement.validFromDate"
> column="ValidFromDate" />
>                 <result property="airAgreement.validToDate"
> column="ValidToDate" />
>                 <result property="airPrice.adultCost"
> column="ADTCostAmount" />
>                 <result property="airPrice.tax" column="TaxAmount" />
>         </resultMap>
>
>         <select id="findFaresFromToAirportsWithinDates"
> resultMap="getPubAirJoinAgreementJoinPrice" parameterClass="java.util.Map"
> cacheModel="faresCache">
>
>           SELECT DISTINCT
>                 TOP #records#
>                         PubAir.PubAirID,
>                         PubAir.FromGeoAirPort,
>                         PubAir.ToGeoAirPort,
>                         PubAirAgreement.ValidFromDate,
>                         PubAirAgreement.ValidToDate,
>                         PubAirPrice.ADTCostAmount,
>                         TAXEstimate.TaxAmount
>
>                 FROM
>                         PubAirRoutingPrice
>                 INNER JOIN
>                         PubAirPrice ON PubAirRoutingPrice.PubAirPriceID =
> PubAirPrice.PubAirPriceID
>                 INNER JOIN
>                         PubAir
>                 INNER JOIN
>                         PubAirAgreement ON PubAir.PubAirAgreementID =
> PubAirAgreement.PubAirAgreementID
>                 INNER JOIN
>                         PubAirRouting ON PubAir.PubAirID =
> PubAirRouting.PubAirID ON
> PubAirRoutingPrice.PubAirRoutingID = PubAirRouting.PubAirRoutingID
>                 INNER JOIN
>                         TAXEstimate ON TAXEstimate.InboundFromGeoCode =
> dbo.PubAir.ToGeoAirPort
>
>                 WHERE
>                         (PubAirPrice.ADTCostAmount > 0)
>                 AND
>                         (PubAir.FromGeoAirPort LIKE #fromAirport#)
>                 AND
>                         (PubAir.ToGeoAirPort LIKE #toAirport#)
>
>                 ORDER BY
>                         PubAirPrice.ADTCostAmount
>
>         </select>
>
> and on the java DAO:
>
> public List findFaresFromToAirportsWithinDates(
>                         String fromAirport,
>                         String toAirport,
>                         String airlineFilter,
>                         String type,
>                         Date startDate,
>                         Date endDate,
>                         Integer records) {
>
>                 Map parameters = new HashMap();
>                 parameters.put("fromAirport", fromAirport + "%");
>                 parameters.put("toAirport", toAirport + "%");
>                 parameters.put("airlineFilter", airlineFilter + "%");
>                 parameters.put("type", type + "%");
>                 parameters.put("startDate", startDate);
>                 parameters.put("endDate", endDate);
>                 parameters.put("records", records);
>                 logger.debug("Retrieving '" + records + "' fares from '" +
> fromAirport
>                                 + "' to '" + toAirport + "', start date '"
> + startDate
>                                 + "', end date '" + endDate + "'");
>                 List l = getSqlMapClientTemplate().queryForList(
>                                 "findFaresFromToAirportsWithinDates",
> parameters);
>                 logger.debug("Retrieved '" + l.size() + "' fares");
>                 return l;
>         }
>
>
> Any help will be highly appreciated.
> chrs
> j.
>
>

Mime
View raw message