db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mogoye <sylvain.rou...@eloquant.com>
Subject RE: Performance problem with derby
Date Wed, 31 Aug 2011 16:06:39 GMT

Thanks a lot.

Now I've got the following result : 
Statement Name: 
	SQL_CURLH000C7
Statement Text: 
	SELECT * FROM CALLCDRACD WHERE clientId='xxxx' ORDER BY CALLSTART OFFSET 10
ROWS FETCH NEXT 50 ROWS ONLY
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Row Count (1):
Number of opens = 1
Rows seen = 50
Rows filtered = 10
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
	optimizer estimated row count:        45816.40
	optimizer estimated cost:       873660.23

Source result set:
	Sort ResultSet:
	Number of opens = 1
	Rows input = 460886
	Rows returned = 60
	Eliminate duplicates = false
	In sorted order = false
	Sort information: 
		Number of merge runs=50
		Number of rows input=460886
		Number of rows output=460886
		Size of merge runs=[9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055,
9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055,
9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055,
9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055,
9055, 9055, 9055, 9055, 9055]
		Sort type=external
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		optimizer estimated row count:        45816.40
		optimizer estimated cost:       873660.23

	Source result set:
		Table Scan ResultSet for CALLCDRACD at read committed isolation level
using instantaneous share row locking chosen by the optimizer
		Number of opens = 1
		Rows seen = 460886
		Rows filtered = 0
		Fetch Size = 16
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			next time in milliseconds/row = 0

		scan information:
			Bit set of columns fetched=All
			Number of columns fetched=28
			Number of pages visited=46106
			Number of rows qualified=460886
			Number of rows visited=460886
			Scan type=heap
			start position:
				null
			stop position:
				null
			qualifiers:
				Column[0][0] Id: 0
				Operator: =
				Ordered nulls: false
				Unknown return value: false
				Negate comparison result: false
			optimizer estimated row count:        45816.40
			optimizer estimated cost:       873660.23



I'm pretty sure that my index is not used. But I wonder if my index and its
usage is correct.

In fact this DB is filled with a special use case : All 460 000 entries have
the same ClientId.
(This customer is deployed on a dedicated server, while smaller customers
are shared in an other DB => ClientId field used to identify them)

I was expecting that index would be usefull to avoid sorting results with
the request : 

SELECT * FROM CALLCDRACD WHERE clientId='xxxx' ORDER BY CALLSTART OFFSET 10
ROWS FETCH NEXT 50 ROWS ONLY

but it seems that I'm wrong. Can you confirm me if my index can be used in
that way, or if there is a way to have better performance ?

 









bbergquist wrote:
> 
> Do it this way:
> 
> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> SELECT * FROM ....;
> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
> 
> Replace the "SELECT * FROM ..." with your query.  The "VALUES SYS..." will
> return one row, one column that contains the statement execution plan.  
> 
> I use SquirrelSQL client and run the first statement, run the query, run
> the "values".  Look at the query plan, and then turn the statistics off.
> 




-- 
View this message in context: http://old.nabble.com/Performance-problem-with-derby-tp32364853p32373385.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.


Mime
View raw message