aspose file tools*
The moose likes JDBC and the fly likes JDBC performance issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC performance issue" Watch "JDBC performance issue" New topic
Author

JDBC performance issue

Howie Jiang
Greenhorn

Joined: Aug 25, 2003
Posts: 26
Hi, I have this query:

select x.a, y.b from table1 x join table2 y on x.id = y.id where y.type in ('xxx', 'xxxx', 'xxxxx') and x.c = 'something'

table x is very big, 30 million rows. y.id is the key of table y.

The execution of this query is a few second, but when I iterate through the resultset, it takes me 10 minutes. This is too long. There are 800000 records in the resultset.

If I do it in this way:
select x.a, y.b from table1 x join table2 y on x.id = y.id where y.type in ('xxx') and x.c = 'something'
UNION
select x.a, y.b from table1 x join table2 y on x.id = y.id where y.type in ('xxxx') and x.c = 'something'
UNION
select x.a, y.b from table1 x join table2 y on x.id = y.id where y.type in ('xxxxx') and x.c = 'something'

The exeuciton will take me 5 minutes, and iteration through the resultset is just 1 second.

Why like that?

And any way to minimize both statement execution time and resultset iteration time?

Thanks very much~~!!
Howie
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3697
    
    5

What you've stumbled on is a very common issue in performance tuning and can have nothing to do with JDBC. In particular, depending on the indexes of your records (and memory available) the query may be run on a streamlined index of the data rather the data itself. For example, if you take age>15 for a user table and there is a sorted index on age, getting the list of records that match this query may be extremely fast, but actually reading the records since this is not stored in the index or in memory can be extremely costly.

One tip is to store the projection information you want to read as another index, in this way you can both query and retrieve information on the index of the data rather than ever reading the data itself. Another way in the JDBC context is to set your prefetch size so records are grouped into larger set, although there's no guarentee this will help.

As I've said this is an issue with database managemenent, if you need the entire record (small projection of columns not useful) and the data is big (>2 gigs) it cant fit into memory and may take long to read no matter how to store it. In oracle you can set table spaces and store a table in multiple files based on attributes. 9 times out of 10 appropriate database indexes will solve this but there are other times its much harder.
[ December 07, 2006: Message edited by: Scott Selikoff ]

My Blog: Down Home Country Coding with Scott Selikoff
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: JDBC performance issue
 
Similar Threads
inner join Vs outer join
[fixed] Urgent SQL question: Is it possible to do this?
In depth SQL question
Group by desc in jpql
Select 1