| hmmm... did you try to specify the JDBC type on that column?
obsid = #obsid:NUMERIC#
I am running out of ideas :)
Nathan On Mar 21, 2006, at 7:25 AM, Nicolas Fajersztejn wrote: Hi Nathan,
the obsid type is Numeric(6,0) (I'm using a Sybase DB but I think it maps to the number(p,s) type in Orcacle)
CREATE TABLE dbo.exposures (
obsid numeric(6,0) NOT NULL,
inst varchar(5) NOT NULL,
expno numeric(5,0) NOT NULL,
expid char(4) NOT NULL,
datamode varchar(20) NOT NULL,
start_utc datetime NOT NULL,
end_utc datetime NOT NULL,
duration int NOT NULL,
ocb int NULL,
epic_filter varchar(16) NULL,
om_filter varchar(8) NULL,
bkg_count_rate float NULL,
scientific char(1) DEFAULT 'Y' NULL,
duty_cycle float NULL
)
What I pass to the query is an Integer:
SELECT
obsid obsID,
inst inst,
epic_filter epicFilter
FROM xsa..exposures
WHERE
obsid = #value#
public static List getExposuresForObsId(Integer obsID) {
SqlMapClient sqlMap = XATSqlConfig.getSqlMapInstance();
List exposureList = null;
try {
long t1 = System.currentTimeMillis();
exposureList = sqlMap.queryForList("getExposuresForObsId", obsID);
long t2 = System.currentTimeMillis();
System.out.println("time queryForList is " + (t2-t1));
}
catch (SQLException e) {
logger.log(Level.WARNING, "SQLException getting exposures list: " + e);
}
return exposureList;
}
Is there another type or a better way of doing this?
Cheers,
Nicolas.
On Tue, 2006-03-21 at 07:06 -0700, Nathan Maves wrote:
Nicolas,
I am guessing that there is a type mismatch from your db to your parameter. Please post both the table definition and your bean.
if the DB field is a VARCHAR and your parameter is an Integer it can cause problems.
id = '123' will have a different performance then id = 123
If that is the case then use some thing like
id = #id:VARCHAR#
to insure that quotes are used with a varchar column.
Nathan
On Mar 21, 2006, at 5:43 AM, Nicolas Fajersztejn wrote:
Hi again,
I have tried all these things you are suggesting:
- setting cacheModelsEnabled to false
- run the query several times in a loop
- disable logging.
and still I get the same result.
But to me this question is very simple. If I change #value# for the actual number (i.e. 100) in the query
SELECT
obsid obsID,
inst inst,
epic_filter epicFilter
FROM xsa..exposures
WHERE
obsid = 100
(rather than obsid=#value#)
it takes only 50ms rather than 1500ms. So, if I am passing an Integer it seems very clear to me that all this time is spent in the conversion of the parameter into the actual value. Isn't that correct?
Have you guys tried to measure how long it takes in your applications to do this type of conversion? It seems very weird to me that it can take so long just a simple thing. I noticed because I need to execute this query about 1000 times for different obsid's and it is obviously not an acceptable time to wait 1500 seconds. With straight JDBC it only takes 8ms per query...
Any suggestions or comments?
Thanks,
Nicolas.
On Mon, 2006-03-20 at 09:04 -0700, Clinton Begin wrote:
Also make sure logging is disabled.
Clinton
On 3/17/06, Larry Meadors <[EMAIL PROTECTED]> wrote:
Also, run the query several times, because on the first pass, it is
setting up the parameterMap.
long t1,t2;
t1 = System.currentTimeMillis();
for(int i = 0; i++; i < 10) sqlMap.queryForList("getExposuresForObsId", obsID);
t2 = System.currentTimeMillis();
System.out.println("time to run queryForList 10x is " + (t2-t1));
Larry
On 3/17/06, Sven Boden <[EMAIL PROTECTED]> wrote:
>
> The pool query is not used as Pool.PingEnabled is false. ;-)
>
> Try rerunning it setting cacheModelsEnabled to false (for a performance
> run, in a real system if you have a cache hit the cache speeds up
> processing, for a single run a cache slows down), and switch off
> debugging if you have it on.
>
> Regards,
> Sven
>
> Larry Meadors wrote:
>
> ><property name="Pool.PingQuery" value="select * from data_set"/>
> >
> >What is "data_set", and how long does "select * from data_set" take to run?
> >
> >Larry
> >
> >
> >On 3/17/06, Nicolas Fajersztejn <[EMAIL PROTECTED]> wrote:
> >
> >
> >> Hi,
> >>
> >> I'm new to iBatis. I have been using it for a couple of months and really
> >>like it.
> >>
> >> However, now I am having problems with a very simple query. This is the
> >>mapping I have:
> >>
> >> <select id="getExposuresForObsId"
> >>resultClass="xat.proprietarydates.objects.Exposure">
> >> SELECT
> >> obsid obsID,
> >> inst inst,
> >> epic_filter epicFilter
> >> FROM xsa..exposures
> >> WHERE
> >> obsid = #value#
> >> </select>
> >>
> >>
> >> obsid is a numeric value. I am passing an Integer as parameter and this
> >>query takes about 1400 ms to execute!!!
> >> The same query run with explicit value (obsid = 100 for example) takes only
> >>48 ms.
> >>
> >> Does it really take so much time to convert an Integer and build up the
> >>query or am I missing something?
> >>
> >> This is my config file in case it helps:
> >>
> >> <transactionManager type="JDBC">
> >> <dataSource type="SIMPLE">
> >> <property name="JDBC.Driver" value="${driver}"/>
> >> <property name="JDBC.ConnectionURL" value="${url}"/>
> >> <property name="JDBC.Username" value="${username}"/>
> >> <property name=" JDBC.Password" value="${password}"/>
> >> <property name="JDBC.DefaultAutocommit" value="true"/>
> >> <!-- The following are optional -->
> >> <property name="Pool.MaximumActiveconnections" value="10"/>
> >> <property name="Pool.MaximumIdleConnections" value="5"/>
> >> <property name=" Pool.MaximumCheckoutTime" value="120000"/>
> >> <property name="Pool.TimeToWait" value="10000"/>
> >> <property name="Pool.PingQuery" value="select * from data_set"/>
> >> <property name="Pool.PingEnabled" value="false"/>
> >> <property name="Pool.PingConnectionOlderThan" value="0"/>
> >> <property name=" Pool.PingConnectionNotUsedFor" value="0"/>
> >> </dataSource>
> >> </transactionManager>
> >>
> >> <settings
> >> cacheModelsEnabled="true"
> >> enhancementEnabled="true"
> >> lazyLoadingEnabled="true"
> >> maxRequests="32"
> >> maxSessions="10"
> >> maxTransactions="5"
> >> useStatementNamespaces="false"
> >> />
> >>
> >>
> >> And the Java code:
> >>
> >> public static List getExposuresForObsId(Integer obsID) {
> >> SqlMapClient sqlMap = XATSqlConfig.getSqlMapInstance();
> >> List exposureList = null;
> >> try {
> >> long t1 = System.currentTimeMillis ();
> >> exposureList = sqlMap.queryForList("getExposuresForObsId",
> >>obsID);
> >> long t2 = System.currentTimeMillis();
> >> System.out.println("time queryForList is " + (t2-t1));
> >>
> >> }
> >> catch (SQLException e) {
> >> logger.log(Level.WARNING, "SQLException getting exposures list:
> >>" + e);
> >> }
> >> return exposureList;
> >> }
> >>
> >>
> >>
> >> I would gladly appreciate your help.
> >>
> >> Thanks.
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >
> >
>
>
|
