db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Performance difference while running the same query though ij and java pgm
Date Fri, 03 Mar 2006 13:18:45 GMT
I haven't seen your Java prgram so I'm on thin ice here: But, if you
do a select in ij, ij will materialize all the data from the select,
while the embedded driver will not materialize anything before you do
the first ResultSet.next(). Might that be the case?

>>>>>>>>>>>> Manjula G Kutty wrote (2006-03-01 12:41:14):
> hi,
> 
> I have 2 tables with around 100,000 rows and I was running the following 
> query.
> 
> select mtb1.col5 from mytable1 mtb1 inner join mytable2 mtb2 on 
> mtb1.col5=mtb2.col5 where mtb1.col1>0 AND mtb1.col4='tab1_col4' AND 
> (mtb1.col1>0 OR mtb2.col1>0);
> 
> This query should return 0 rows. When I run the query through a java 
> program everything works fine and the time taken to run the query is 
> around 10s. But when I run the same query through ij, it takes for 
> ever!! I waited for more than 10 minutes.  When ij was waiting to get 
> the result my cpu usage was 100%.
> 
> It may not be a serious issue since the same query works fine through 
> java pgm. But, I would like to know if anybody know why is this happening??
> 
> With 10.1 also I'm getting the same result.
> 
> FYI: The table schema is as follows
> 
> Create table mytable1(col1 int primary key, col2 varchar(100),col3 
> VARCHAR(160),col4 varchar(10),col5 VARCHAR(20),col6 varchar(30),col7 
> VARCHAR(40));
> Create table mytable2(col1 int primary key, col2 varchar(100),col3 
> VARCHAR(160),col4 varchar(10),col5 VARCHAR(20),col6 varchar(30),col7 
> VARCHAR(40));
> Create table mytable3(col1 int primary key, col2 varchar(100),col3 
> VARCHAR(160),col4 varchar(10),col5 VARCHAR(20),col6 varchar(30),col7 
> VARCHAR(40));
> Create table mytable4(col1 int primary key, col2 varchar(100),col3 
> VARCHAR(160),col4 varchar(10),col5 VARCHAR(20),col6 varchar(30),col7 
> VARCHAR(40));
> 
> Thanks,
> Manjula

-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

Mime
View raw message