[SOLVED] Fast read of a Nexus database table

Fast read of a Nexus database table

I want to read the entire contents of a table into memory, as quickly as possible. I am using Nexus database, but there might be some techniques I could use that are applicable to all database types in Delphi.

The table I am looking at has 60,000 records with 20 columns. So not a huge data set.

From my profiling, I have found the following so far:

Accessing tables directly using TnxTable is no faster or slower than using a SQL query and 'SELECT * FROM TableName'
The simple act of looping through the rows, without actually reading or copying any data, takes the majority of the time.

The performance I am getting is

Looping through all records takes 3.5 seconds
Looping through all the records, reading the values and storing them, takes 3.7 seconds (i.e. only 0.2 seconds more)

A sample of my code

var query:TnxQuery;
begin
    query.SQL.Text:='SELECT * FROM TableName';
    query.Active:=True;

    while not query.Eof do
        query.Next;

This takes 3.5 seconds on a 60,000 row table.

Does this performance sound reasonable? Are there other approaches I can take that would let me read the data faster?

I am currently reading data from a server on the same computer, but eventually this may be from another server on a LAN.

Solution

You should be using BlockRead mode with a TnxTable for optimal read speed:

nxTable.BlockReadOptions := [gboBlobs, gboBookmarks];
//leave out gboBlobs if you want to access blobs only as needed
//leave out gboBookmarks if no bookmark support is required

nxTable.BlockReadSize := 1024*1024; //1MB
// setting block read size performs an implicit First
// while block read mode is active only calls to Next and First are allowed for navigation
try
  while not nxTable.Eof do begin
    // do something....
    nxTable.Next;
  end;
finally
  nxTable.BlockReadSize := 0;
end;

Also, if you don't need to set a range on a specifc index, make sure to use the sequential access index for fastest possible access.