Dennis, I need to remember TOP from now on!
Jan -----Original Message----- From: Dennis McGrath <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: Tue, 4 May 2010 13:58:22 -0500 Subject: [RBASE-L] - RE: Finding the autonum You’re welcome, Jack. I am new to using TOP (how did that one elude me for so long) so I did not think of it at first. I was amazed myself how fast it was, and just a little surprised that MAX was slow in comparison. This old dog is still learning new tricks. Dennis McGrath From: [email protected] [mailto:[email protected]] On Behalf Of Jack Mathis Sent: Tuesday, May 04, 2010 1:45 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Finding the autonum Wow, that’s fast, and works Thank you very much, Jack Mathis From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Tuesday, May 04, 2010 11:32 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Finding the autonum SELECT Top 1 PartRef + INTO test IND v1 + FROM Invent + ORDER BY PartRef Desc Try this. It is FAST, and reliable, and will throw no error. Dennis McGrath From: [email protected] [mailto:[email protected]] On Behalf Of Jack Mathis Sent: Tuesday, May 04, 2010 1:07 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Finding the autonum On the original message I missed typed, should have been: SET ERROR MESSAGE 2441 OFF SELECT PartRef + INTO test IND v1 + FROM Invent + ORDER BY PartRef Desc + SET ERROR MESSAGE 2441 ON This still seems the fastest And yes it is a very large table. One table we use this routine on is over 3 million records. Thank you for the response Jack Mathis From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Tuesday, May 04, 2010 9:27 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Finding the autonum Actualy, this will only work if the column is indexed. On a very large table select max took me 2 seconds even with the index. When I used the where clause to minimize the rows read (from the index) it was very quick. Dennis McGrath From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Tuesday, May 04, 2010 11:23 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Finding the autonum SELECT MAX(PartRef) + INTO test IND v1 + FROM Invent Should be fast if the column PartRef is indexed. Alternately if you get the sys_column_ID from the SYS_COLUMNS table and Look in SYS_Defaults there is a SYS_Next column that contains the next number. This won’t help you directly but it can be used to speed up the MAX Lets say the NEXT number is 1,000,000 and you have retrieved it into vInt Then Set var vInt = (.vInt – 1000) – if deletes seldom happen in this table, you can make this 100 or even 10 SELECT MAX(PartRef) + INTO test IND v1 + FROM Invent + WHERE PartRef > .vInt This should speed up the command significantly on a table with lots of rows. Adjust to you specific needs Dennis McGrath Dennis McGrath From: [email protected] [mailto:[email protected]] On Behalf Of Jack Mathis Sent: Tuesday, May 04, 2010 10:50 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Finding the autonum Is there a way to find the autonum number or the last autonum number used. I tried : SELECT PartRef + INTO test IND v1 + FROM Invent + WHERE COUNT = LAST But this gives me the last record inserted, even if it was a updated record, that had to insert because the notes field added more than the record pad allowed. SET VAR Test TEXT = NULL GETPROPERTY TABLE 'Invent->RECORDCOUNT' Test --From the R>Prompt gives me On XP x86 gives -- NULL On Windows 7 x64 -- Access violation at address 004047A8 in module ‘rbg8ee.exe’. Read of address 53552039 SELECT MAX(PartRef) + INTO test IND v1 + FROM Invent This seems slow SET ERROR MESSAGE 2441 OFF SELECT PartRef + INTO test IND v1 + FROM Invent + ORDER BY PartRef + SET ERROR MESSAGE 2441 ON I figured if I could get the autonum number it might be faster yet, without errors. Thanks Jack Mathis Erickson Air-Crane Incorporated Computer Software Applications Programmer
