Huge table browsing

GuzunNicolae · Post by **GuzunNicolae** » Mon 03 Sep 2007 12:27

Hello

I have a very big table (several millions records). I have to browse every record of the database. I cannot load it into memory cause it is too big and I use FetchAll=False to speed up loading. But the way I browse through records the application gets bigger. For 500.000 records it is 200 Mb!!! This is too much.

Is there a way to browse the table but to keep in memory only the latest fetched records?

Thanks.

Antaeus · Post by **Antaeus** » Mon 03 Sep 2007 13:57

Please pay attention at the TCustomDADataSet.UniDirectional property. In UniDirectional mode only current record is cached.

GuzunNicolae · Post by **GuzunNicolae** » Mon 03 Sep 2007 16:25

Thanks. This is a great feature.
I have made it work once, now I could not make it work the second time

Antaeus · Post by **Antaeus** » Tue 04 Sep 2007 06:51

Do you mean that now UniDirectional does not work?
Please specify, what problems have you encountered with UniDirectional.

GuzunNicolae · Post by **GuzunNicolae** » Wed 05 Sep 2007 14:39

I thought the problem was in UniDirectional. But after some research it was not there.

My DB is Unicode. So to browse all data I use:

Code: Select all

      while not eof do
      begin
        if not FieldByName('image').IsNull Then tempS := FieldByName('image').Value
                                           Else tempS := '';
        //Do sth with tempS ....
        Next;
      end;

This way the size of the application grows. And for mlns of records this could be a problem.
Yet if istead of the above I use tempS := FieldByName('image').AsString the size of the application does not grow. I can use AsString but this way I am loosing all the Unicode.
Changing the type of tempS to string does not help.

Do you have any idea why this happens and how to overcome it? I guess the memory assigned for tempS is not reused at the next assignment. But I do not really know how to solve it.

Thanks

Antaeus · Post by **Antaeus** » Fri 07 Sep 2007 08:05

We have investigated this issue. This is a Delphi bug. To avoid it you should cast the field to TWideStringField. It should look like:

Code: Select all

  tempS := TWideStringField(FieldByName('image')).Value

Antaeus · Post by **Antaeus** » Fri 07 Sep 2007 08:20

In order to optimize performance of your application, try to avoid using FieldByName in a cycle:

Code: Select all

      var
        fld: TWideStringField;
      ...
      fld := TWideStringField(FieldByName('image'));
      while not eof do
      begin
        if not fld.IsNull Then tempS := fld.Value
                               Else tempS := '';
        //Do sth with tempS ....
        Next;
      end;

GuzunNicolae · Post by **GuzunNicolae** » Fri 07 Sep 2007 10:04

This solved the problem. How did you find that

Thanks a lot!!!!!
You are really great.