>Support,
>
>I am using Firebird server 2.5.2, Windows 7, and Visual Basic 2010.
>
>I would like to read a certain # of records from a table in the database using a start
>/ stop record range. I am trying to re-write an application written by someone else
>which reads the entire database from beginning to end which takes a long time and I may
>only need the last 1000 records of a database that can have over 100,000.
>
>I have been using the following to determine the total number of records in the
>database so I should be able to use it to establish a range.
>
>'Find the total number of records in the SURFACE table
>Dim count As FbCommand = New FbCommand("SELECT COUNT(*) FROM (
>select * from SURFACE )", fbc)
>Dim readCount As FbDataReader = count.ExecuteReader
>readCount.Read()
>lastDbRecord = readCount.GetValue(0)
>' Get the total number of records in this database

Hi Rudy!

One of the differences between desktop databases like Access and client/server databases like Firebird, is that desktop databases typically work with tables, whereas client/server databases work with datasets. Reading the last 1000 records rather than the entire table is a good start in this transition, so congratulations!

The best way is to select only records and fields of interest, e.g.

SELECT MyPK, MyInterestingTextField, MyInterestingInteger1, MyInterestingInteger2
FROM MyTable
WHERE SomeField = :MyParameter
AND SomeOtherField = :MyParameter2
ORDER BY MyDate DESC
ROWS 1 TO 1000

If you want to select the last 1000 rows, but display them in ascending order, you can do:

with tmp (MyPK, MyITF, MyII1, MyII2, MyDate) as
(SELECT MyPK, MyInterestingTextField, MyInterestingInteger1, MyInterestingInteger2, MyDate
FROM MyTable
WHERE SomeField = :MyParameter
AND SomeOtherField = :MyParameter2
ORDER BY MyDate DESC
ROWS 1 TO 1000)
SELECT MyPK, MyInterestingTextField, MyInterestingInteger1, MyInterestingInteger2
FROM tmp
ORDER BY MyDate ASC /*ASC is default, so it is not necessary to include*/

Sometimes users are used to see entire tables (or at least they were before the Internet) and start to wonder/worry when they only see some of their data. However, it IS preferable to show only data of interest, no-one benefits from seeing data irrelevant for their use.

HTH,
Set