Greg, Just to confirm, are you also using parameter binding in the non-iBATIS way... no experience with Sybase but in DB2 you can get similar effects with binding and non-binding...
When you use binding the optimizer makes a general plan that would "suit" all parameter values, if you don't use binding the optimizer can optimize the statement to your specific values: if there are only a couple of rows for the value you request, a table scan would mostly be considered a waste. While the optimizer would not knows this in the binding situation. Not that I'm in favor of non-binding, in most databases not using binding is an evil sin. Regards, Sven On Thu, 28 Jul 2005 14:17:16 +1200, you wrote: >Hi Daniel and Netsql, > >Thanks for the reply. The odd thing is, I can trace it down to the >PreparedStatement.execute inside ibatis, and I can see the table scan. So it >appears, through whatever mechanism, ibatis is changing how its executed. >P6spy reports that the query is exactly the same in both cases. > >my configs: > ><sqlMapConfig> > <settings cacheModelsEnabled="true" enhancementEnabled="true" > lazyLoadingEnabled="true" errorTracingEnabled="false" maxRequests="32" > maxSessions="10" maxTransactions="5" useStatementNamespaces="false"/> > <transactionManager type="EXTERNAL"> > <property name="SetAutoCommitAllowed" value="false"/> > <dataSource type="JNDI"> > <property name="DataSource" value="java:/jdbc/basil"/> > </dataSource> > </transactionManager> > >and the query is: > ><statement id="findPlacementByPlacementId" parameterClass="java.lang.Long" >resultClass="Placement" > > SELECT > p.placement_id AS oid >... > ,e.public_travel_cost AS publicTravelCost >... > FROM PLACEMENT p, EER_PLACEMENT_ADDITIONAL e > WHERE p.placement_id = #value# > AND p.placement_id *= e.placement_id > </statement> > > >i've had no problems with ibatis previously, but the essential thing is: >running in ibatis=table scan, outside=not. I am guessing theres something >stuffed with my database (indexes, keys etc), but im a bit stumped why running >in ibatis causes the table scan! > >Thanks for your help >Greg > >-----Original Message----- >From: Daniel Henrique Ferreira e Silva [mailto:[EMAIL PROTECTED] >Sent: Thursday, 28 July 2005 1:52 p.m. >To: [email protected] >Subject: Re: Slow query update... > > >Hi Greg, > >iBATIS doesn't change the way your SQL run on your database. It is >basically JDBC wrapped in an easier and cleaner framework. > >After some time reading about other people experience with iBATIS, i'd >say this issue is probably caused by something in your code, not in >iBATIS. > >How are you setting it up? >How are your maps? >Tell us about your configs so we can help you to fix this issue. > >Cheers, >Daniel Silva. > >On 7/27/05, Greg Day <[EMAIL PROTECTED]> wrote: >> >> >> Hi all >> >> regarding my ultra-slow query when running through ibatis.. >> just to recap, if I run a sybase query through Ibatis (inside jboss), it >> goes really slow, if I run it through jdbc (inside jboss) its quick. >> >> The query is essentially a select * by unique identifier (which has an >> index). >> >> Basically, the database is doing a table scan on this table when the query >> is run through ibatis. It doesnt do the table scan when its run through >> jdbc. >> >> So, my question is: what is ibatis doing here? are there hints that it gives >> to sybase that tells it something about query plans? im very confused... >> >> any help would be much appreciated. >> >> Thanks >> Greg
