[JCR-2830] JCR-SQL2 : Query on large node-set is (too) slow even when offset and limit is used

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.3
• Component/s: query
• Environment: + Win7 (64bit); + JR built from latest greatest sources; + repo with many nodes of same node type > 77'000
• Assignee: Unassigned
• Reporter: Clemens Wyss
• Created: 03/Dec/10 10:51
• Updated: 17/Oct/12 10:03

Description

Given a node-set of approx 77'000 entries a SQL2-query limited to 10 nodes takes approx 37 to 59sec whereas the corresponding SQL returns in less than 1sec.

Query q = session.getWorkspace().getQueryManager().createQuery( "select * from [task]", Query.SQL2 );
q.setOffset( 0 ); // or any other offset
q.setLimit( 10 );
returnValue = q.execute();

Issue Links

• is blocked by: JCR-2959 SQL2 QueryEngine should use Lucene for sort
• relates to: JCR-3446 Implement sort by node name for XPATH