getting first k rows from a query that satisfy some additional condition

2008-10-27 Thread Amir Michail
Hi, What is a good way for efficiently getting the first k rows from a query that also satisfy some additional condition to be determined using Java code? Amir -- http://chatbotgame.com http://numbrosia.com http://twitter.com/amichail

Re: derby backwards compatibility

2008-10-27 Thread Rick Hillegas
Amir Michail wrote: Hi, Where can I find information on backwards compatibility? For example, is version 10.4.2.0 compatible with applications compiled with 10.3.1.4? Can I just use the new libraries without recompiling apps? Amir Hi Amir, The short answer to your question is that you

Re: getting first k rows from a query that satisfy some additional condition

2008-10-27 Thread Amir Michail
On Mon, Oct 27, 2008 at 1:14 PM, Amir Michail [EMAIL PROTECTED] wrote: Hi, What is a good way for efficiently getting the first k rows from a query that also satisfy some additional condition to be determined using Java code? BTW, even without this condition, the following approach is slow:

Re: getting first k rows from a query that satisfy some additional condition

2008-10-27 Thread Daniel Noll
Amir Michail wrote: Hi, What is a good way for efficiently getting the first k rows from a query that also satisfy some additional condition to be determined using Java code? setMaxRows(int) is the standard way of doing this in JDBC, it only falls over if you later want to retrieve rows

Re: getting first k rows from a query that satisfy some additional condition

2008-10-27 Thread Amir Michail
On Mon, Oct 27, 2008 at 5:45 PM, Daniel Noll [EMAIL PROTECTED] wrote: Amir Michail wrote: Hi, What is a good way for efficiently getting the first k rows from a query that also satisfy some additional condition to be determined using Java code? setMaxRows(int) is the standard way of doing

Re: getting first k rows from a query that satisfy some additional condition

2008-10-27 Thread Daniel Noll
Amir Michail wrote: It's slow with setMaxRows. Are you sure you have the correct indexes on whatever you're using for your condition? Try getting an explanation of your query to see what it's actually doing. I can confirm that the row_number() approach is slow (it's the current best

Re: getting first k rows from a query that satisfy some additional condition

2008-10-27 Thread Peter Ondruška
Please, post the query you are running.There are queries where setMaxRows will not be faster, for example if you do GROUP BY or ORDER BY. On Mon, Oct 27, 2008 at 22:51, Amir Michail [EMAIL PROTECTED] wrote: On Mon, Oct 27, 2008 at 5:45 PM, Daniel Noll [EMAIL PROTECTED] wrote: Amir Michail

Re: getting first k rows from a query that satisfy some additional condition

2008-10-27 Thread Amir Michail
On Mon, Oct 27, 2008 at 5:59 PM, Peter Ondruška [EMAIL PROTECTED] wrote: Please, post the query you are running. There are queries where setMaxRows will not be faster, for example if you do GROUP BY or ORDER BY. The order is important, so I'm using order by. Amir On Mon, Oct 27, 2008 at

Re: getting first k rows from a query that satisfy some additional condition

2008-10-27 Thread Peter Ondruška
Well, in that case database engine has to read all relevant rows, then sort and only after that it can display the first row(s). On Mon, Oct 27, 2008 at 23:02, Amir Michail [EMAIL PROTECTED] wrote: On Mon, Oct 27, 2008 at 5:59 PM, Peter Ondruška [EMAIL PROTECTED] wrote: Please, post the

Re: getting first k rows from a query that satisfy some additional condition

2008-10-27 Thread Amir Michail
On Mon, Oct 27, 2008 at 6:07 PM, Peter Ondruška [EMAIL PROTECTED] wrote: Well, in that case database engine has to read all relevant rows, then sort and only after that it can display the first row(s). Why doesn't it use the index on the column specified in order by? Amir On Mon, Oct 27,

Re: getting first k rows from a query that satisfy some additional condition

2008-10-27 Thread Peter Ondruška
Post the query and table and index DDL. On Mon, Oct 27, 2008 at 23:10, Amir Michail [EMAIL PROTECTED] wrote: On Mon, Oct 27, 2008 at 6:07 PM, Peter Ondruška [EMAIL PROTECTED] wrote: Well, in that case database engine has to read all relevant rows, then sort and only after that it can