Re: Incremental queries?

rtomas@LUCENT.COM wrote: > Running IDS 9.21.FC4. > > We would like our application to support incremental queries of a > table. By this we mean that the application should allow a user to > press a "Query" button and receive the first block of 'n' rows of a > table based on an ORDER BY of the key column(s). The user should then > be able to press a "Query Next" button and retrieve the next block of > 'n' rows (based on the same ORDER BY clause). We don't want to > retrieve all rows in the table and buffer them locally on the client > because the table could be huge and if user doesn't want to see all of > the rows then too much network traffic was generated. > > Have figured out the WHERE clause to make the "Query Next" happen based > on values of last row previously retrieved. It has the following form: > > where (col1 > last_val1) > or (col1 = last_val1 and col2 > last_val2) > or (col1 = last_val1 and col2 = last_val2 and col3 > last_val3) > > (This expands based on the number of columns in the key.) > > The SQL statement for the initial "Query" has no WHERE clause. It only > has an ORDER BY clause and it runs relatively fast. The "Query Next" > SQL statement has the WHERE if the form shown above and it runs > terribly slow. Any ideas on what we can do to speed it up? Is what > we're trying to accomplish not practical with IDS 9.21? I believe IDS 10.00.UC3 is eGA (electronically available), and it has probably got the feature you're after:- SELECT [SKIP <n>] [{FIRST|LIMIT} <n>]... FROM ... More information is available at: http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.sqls.doc/sqls652.htm http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.sqls.doc/sqls656.htm Both links get direct to the point. (Thanks, Keshav, for the info.)

Jonathan Leffler wrote: > > SELECT [SKIP <n>] [{FIRST|LIMIT} <n>]... FROM ... > Surely that belongs to the order-by clause? -- rh

> SELECT [SKIP <n>] ... That seems like it is exactly what we need. I'll have to talk to our customer about the chances of upgrading to IDS 10.00. Thanks! -Roger