db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "arindam.bhattacharjee" <mr.arindam.bhattachar...@gmail.com>
Subject Re: URGENT!!! JDBC SQL query taking long time for large IN clause
Date Tue, 07 Apr 2009 14:45:06 GMT

Hello Knut,

Thanks for your quick response. This is a sample database which I have
created just for testing out the performance and has been written to only
once in one go. I tried temp tables but that is just too slow. The IN clause
has values which comes from another source and I can't modify that.

However, I will try out what you state below. But still, I wanted to get
your pulse about whether Derby can respond in sub 100 millisec time with the
table sizes you see above? 

I find that:

select category_master.category_name, count(category_master.category_name)
as category_count
from
	(
		select internal.object_id
		from
		(
			values(1001) union all
			values(1001) union all
			values(1001) union all
			values(1001) union all
			values(1002) union all
			values(1001) union all
			values(1001) union all
			values(1001) union all
			values(1001) union all
			values(1001) union all
			values(1001) union all
			values(1001) union all .......
			values(9999)
		) as internal(object_id)

	) as external_ids,
	object_master,
	category_master,
	object_category_mapping
where
	external_ids.object_id = object_master.object_id and
	external_ids.object_id = object_category_mapping.object_id and
	object_master.object_id = object_category_mapping.object_id and
	category_master.category_id = object_category_mapping.category_id
group by
	category_master.category_name
order by
	category_count desc

is much faster unfortunately connection.prepareStatement() is taking way too
much memory (both stack and heap - I have a constraint of 256 MB MAX memory
for my JVM) which goes beyond my applications resources. Is there a way I
can precompile some SQLs which are very expensive to parse during execution.

Best regards,

Arindam.


Knut Anders Hatlen wrote:
> 
> "arindam.bhattacharjee" <mr.arindam.bhattacharjee@gmail.com> writes:
> 
>> Hello,
>>
>> I would like my query below to return within 100 millisecs. Please help
>> me,
>> and the values for the IN clause comes from outside hence cannot really
>> change the IN clause to a join on an existing table.
> 
> Hi Arindam,
> 
> Does the query run faster if you compress all the tables involved, or if
> you drop and recreate all the indexes? If so, it is likely that the
> index cardinality statistics are out of date, which may make the
> optimizer pick a bad execution plan. Currently, index cardinality
> statistics are only updated at index creation time, when tables are
> compressed, and when columns are dropped. A more automatic solution is
> being worked on. For more details, see:
> 
> https://issues.apache.org/jira/browse/DERBY-269
> https://issues.apache.org/jira/browse/DERBY-3788
> http://db.apache.org/derby/docs/10.4/tuning/ctunstats18908.html
> 
> You may be experiencing some other problem, but this is a problem that
> keeps coming up, so I think it's worth checking.
> 
> Hope this helps,
> 
> -- 
> Knut Anders
> 
> 

-- 
View this message in context: http://www.nabble.com/URGENT%21%21%21-JDBC-SQL-query-taking-long-time-for-large-IN-clause-tp22927332p22930750.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message