Hello there

Is there a way to quickly obtain the seed value of an IDENTITY field? I expect that that would be faster than calling MAX(), as a MAX would have to
look at every record - wouldn't it?

I could time the difference, but I am unsure as to where I should look for the seed value.

Regards

Peter

Peter,

> Is there a way to quickly obtain the seed value of an IDENTITY field?

if you need the value while inserting a new detail record or something
like that, you can get it by using a paramterized statement:

http://www.elevatesoft.com/newsgrp?action=openmsg&group=17&msg=2164&page=1#msg2164

> I expect that that would be faster than calling MAX(), as a MAX would
have to
> look at every record - wouldn't it?

That's not true. If the field is indexed you'll get the result in 0 seconds.
Just run the query and request an execution plan. Then you'll see how
fast it is.

Uli

Uli

<<if you need the value while inserting a new detail record or something
like that, you can get it by using a paramterized statement:??

Thank you, but it is not when I am inserting a new record. It is more for when I want the last 100 records - the last 100 invoices billed for instance.
The code that I am replacing dates back to Paradox days, and rather than using the buggy AutoInc fields in Paradox, I had single record tables
that maintained the last identity number. I could look up that value, subtract 100, and use SQL that returned the records where the ID number was
more than (ID) - 100.

I use Generated fields now, and it occurred to me that the Seed value is stored in a table somewhere, and that might be quicker than doing a MAX
on a million row table. As you say, it might be instantaneous to get the MAX(), but I would still like to see where that Seed value is.

Alternatively, there might be a replacement for TOP, or there might be some tricks with the RANGE statement, that would allow me to specify the
last 100 records - I'm looking forward to finding out.

Regards

Peter

Peter,

> Alternatively, there might be a replacement for TOP, or there might be some tricks with the RANGE statement, that would allow me to specify the
> last 100 records - I'm looking forward to finding out.

Yes: the range clause does this job!

Here an example how to get the last 100 records:

BEGIN
   DECLARE MyCursor CURSOR FOR MyStmt;

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

If you want to replace "TOP", you can just use "SELECT * FROM MyTable
RANGE 1 to10" e.g.

Uli

Uli

RowsInTable? I can't see that in the help files.

Peter

Peter,

> RowsInTable? I can't see that in the help files.

Neither can I.

I copied the sample from a previous thread:

http://www.elevatesoft.com/newsgrp?action=openmsg&group=17&msg=1643&page=1#msg1643

without testing it.
We have to ask Tim for that.

Anyway this should work:

SCRIPT
BEGIN
   DECLARE MyCursor CURSOR WITH RETURN FOR MyStmt;
   DECLARE MyCount INTEGER;
   PREPARE MyStmt FROM 'SELECT COUNT(*) as COUNT from Buchungen';
   OPEN MyCursor;
   FETCH FIRST FROM MyCursor(COUNT) INTO MyCount;
   PREPARE MyStmt FROM 'SELECT * FROM Buchungen RANGE ? TO ?';
   OPEN MyCursor USING (MyCount-99), MyCount;
END

Uli

Uli

I am sure that the script would work, but it uses ROWCOUNT, which I would expect to be slower than referencing the Seed value, depending on the
size of the table.

Maybe I have it wrong, but ROWCOUNT in most DBs results in a physical loop through the table and we were told to shun it. (Boo, hiss, run away).

We were told to replace "if aTable.RecordCount <1" with "if aTable.IsEmpty" or "if aTable.EoF AND aTable.BoF" rather than the dreaded
RecordCount, which I suspect ROWCOUNT is akin to.

Let me know if I am wrong - I can take it.

I guess I could delve into the EDBMgr source to see where the Seed value comes from. Unlike IBase it isn't stored in a generator per se, but I
would expect it to be stored in something like IB's generator.

Regards

Peter

Peter,

> I am sure that the script would work, but it uses ROWCOUNT, which I would expect to be slower than referencing the Seed value, depending on the
> size of the table.
>
> Maybe I have it wrong, but ROWCOUNT in most DBs results in a physical loop through the table and we were told to shun it. (Boo, hiss, run away).

Actually count(*) is very fast - as I indicated you can test it yourself
requesting an execution plan.
I just ran a query against a table with about 400.000 records:

================================================================================
1 row(s) returned in 0 secs
================================================================================

So don't worry.


> I guess I could delve into the EDBMgr source to see where the Seed value comes from. Unlike IBase it isn't stored in a generator per se, but I
> would expect it to be stored in something like IB's generator.

I am quite sure that the seed value is stored in the catalog.

Anyway that wouldn't be a clean solution for you, if you just want to
display the last 100 records e.g.
If records have been deleted you'll get a wrong number of records by
using the max. seed value - 100.

Uli

Uli

<<If records have been deleted you'll get a wrong number>>

Good point, I'll do as you suggest.

Regards

Peter

Peter,

<< I am sure that the script would work, but it uses ROWCOUNT, which I would
expect to be slower than referencing the Seed value, depending on the size
of the table. >>

Not with ElevateDB.  It can always display an accurate row count without any
overhead.  It uses very efficient internal structures for representing sets,
and they can always return the count instantaneously.

Also, the RANGE clause in ElevateDB is a "live" range, meaning that you can
use it and still return a sensitive (live) result set.

<< I guess I could delve into the EDBMgr source to see where the Seed value
comes from. Unlike IBase it isn't stored in a generator per se, but I would
expect it to be stored in something like IB's generator. >>

As Uli indicated, the seed value isn't going to work well in some cases.

--
Tim Young
Elevate Software
www.elevatesoft.com