Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
Seed value or MAX()? |
Tue, Feb 23 2010 11:39 PM | Permanent Link |
Peter | 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 |
Wed, Feb 24 2010 2:45 AM | Permanent Link |
Uli Becker | 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 |
Wed, Feb 24 2010 5:29 AM | Permanent Link |
Peter | 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 |
Wed, Feb 24 2010 10:49 AM | Permanent Link |
Uli Becker | 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 |
Wed, Feb 24 2010 8:22 PM | Permanent Link |
Peter | Uli
RowsInTable? I can't see that in the help files. Peter |
Thu, Feb 25 2010 4:00 AM | Permanent Link |
Uli Becker | 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 |
Thu, Feb 25 2010 4:56 AM | Permanent Link |
Peter | 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 |
Thu, Feb 25 2010 5:10 AM | Permanent Link |
Uli Becker | 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 |
Thu, Feb 25 2010 6:29 PM | Permanent Link |
Peter | Uli
<<If records have been deleted you'll get a wrong number>> Good point, I'll do as you suggest. Regards Peter |
Thu, Feb 25 2010 8:21 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |