db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Performance problem with derby
Date Wed, 31 Aug 2011 17:19:21 GMT
Given the Sort it looks like index is not being used to avoid sorts.
I am not sure why that is the case.  Does adding an index on just
CALLSTART help or not?

The OFFSET feature is relatively new to derby and I am not sure if the 
optimizer has been fully updated to understand it.  Maybe someone who
knows more about the OFFSET feature can comment if the optimizer has 
been updated to know that this query is only going to look at 50 rows
rather than all of them?

Another option that might help is you can give derby hints in your query
to force it to use the index.

So in summary I would suggest doing the following in order:
1) run compress on the base table, this will make sure all the optimizer 
statistics are up to date, and retry to see if that helps.
2) create index on just CALLSTART and retry query to see if that helps.
3) drop new index and add hint to force your existing (clientid, 
callstart) index and see if that helps.

In all cases if you want feedback on the above, including the queryplan 
as you have done below will help.


mogoye wrote:
> 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.
>>
> 
> 
> 
> 


Mime
View raw message