Fast read of a Nexus database table

Question

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.

Tremulous

Does that alone take 3.5s or do you have other instructions in the loop? Because if you're reading data you're probably doing it with FieldByName which is famous for being not-that-fast – Unifoliolate

Answer 1

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.

Fredenburg

WOW. Total time is now reduced from 3.7 seconds to 0.5 seconds. Not bad at all. – Tremulous

Regarding indexes; the table will use the default index by default, which in my case is the sequential access index. So I think I don't have to do anything to make it use the sequential access index. – Tremulous

As long as you keep the SAI marked as default and the IndexName property empty, then yes, the SAI will be used. – Fredenburg

It seems that this same method also works with TnxQuery. I get the same performance using 'SELECT * FROM TABLENAME' as from using the TnxTable. – Tremulous

BlockRead mode works with TnxQuery, yes. But performance depends if you are using a live resultset or not. With a live resultset the query execution will return a direct table cursor which is identical to the cursor a TnxTable would use. But if live resultset is turned off (or of you make your query too complex for a live resultset) then the sql engine has to create an in-memory table and copy all matching records across. You will still get the performance advantage from BlockRead mode when reading from the resultset, but producing that resultset will then take longer. – Fredenburg