What is the most efficient way to get the last 10 records of a (very large)
table?

Uli

Uli


Partly it depends on what you want to do when you've got them, and if you want a resultset with only 10 records in it. I assume there is an index that can be used to limit the records retrieved. For now I'll assume its datetime and you're dealing with a load of transactions and you just want to look at the last 10, and that you want a resultset with only 10 records.

Given the above you do a bit of cheating:

1. CREATE TABLE AS SELECT recordID, transactiondatetime from bigtable where transactiondatetime > roughguess WITH DATA;
if its less than 10 records guess again a bit longer ago, if exactly 10 skip the next step
2. create a descending index on the table using transactiondatetime
3. start at the bottom and delete until only 10 records left
4. do a select on your big table with the WHERE clause picking out just those records with their id in the temporary table.

Of course you can also just select everything you want into the temporary table.

If there's a descending index on the appropriate column you could just use it and the RANGE command.

You could also just try the RANGE command it may be fast enough for you. All you need is the recordcount and something like

SELECT * FROM bigtable RANGE 123456 to 123466

Roy Lambert [Team Elevate]

Uli,

<< What is the most efficient way to get the last 10 records of a (very
large) table? >>

I assume that you want this via SQL ?  If so, the best way is a sensitive
result set from:

SELECT * FROM MyTable
RANGE 599990 TO 600000

ElevateDB can do ranges on sensitive result sets very quickly, especially if
the ranges are near the start or end of the result set.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>SELECT * FROM MyTable
>RANGE 599990 TO 600000

Is there a way to use ROWCOUNT in an expression like this? eg

SELECT * FROM MyTable
RANGE ROWCOUNT-10 TO ROWCOUNT

I tried to simulate this with a function

select * from companies
range rowsintable('companies') - 10 to rowsintable('companies')

but received an error

ElevateDB Error #700 An error was found in the statement at line 2 and column 32 (Expected end of expression but instead found -)

FUNCTION "RowsInTable" (IN "TableName" VARCHAR COLLATE ANSI)
RETURNS INTEGER
BEGIN
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
PREPARE InfoStmt FROM 'SELECT * FROM '+ TableName;
OPEN InfoCursor;
RETURN ROWCOUNT(InfoCursor);
END

Roy Lambert [Team Elevate]

Roy,

<< Is there a way to use ROWCOUNT in an expression like this? eg >>

I'll see about expand the RANGE clause to use something more than simple
values and parameters.  There was a reason for doing it this way, but I'll
have to look at it again to see why.

In the meantime, what you want to do is parameterize (ise the RANGE,
like this:

BEGIN
   DECLARE MyCursor CURSOR FOR MyStmt;

   PREPARE MyStmt FROM 'SELECT * FROM MyTable RANGE ? TO ?';
   OPEN MyCursor USING RowsInTable('MyTable')-10, RowsInTable('MyTable');
END

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>I'll see about expand the RANGE clause to use something more than simple
>values and parameters. There was a reason for doing it this way, but I'll
>have to look at it again to see why.

I have an excuse for forgetting - I'm old and knackered - what's your reason?

Roy Lambert

Tim and Roy,

thanks to both. The range will do the job.

Uli

Roy,

<< I have an excuse for forgetting - I'm old and knackered - what's your
reason? >>

Trust me, if you had to remember and keep track of the amount of information
that I do on a daily basis, you'd go insane.

--
Tim Young
Elevate Software
www.elevatesoft.com