Tim

Is there any possibility to remove index statistics from DBISAM ? Even if I
have to change the source code (I have it).

I have been playing with a very large tables with a large number of indexes
and it is noticable the slow down when update theses tables.

Eduardo

Eduardo,

<< Is there any possibility to remove index statistics from DBISAM ? Even if
I have to change the source code (I have it).

I have been playing with a very large tables with a large number of indexes
and it is noticable the slow down when update theses tables. >>

It's something that has been asked for in DBISAM, and yes, it is possible to
do.  There is actually a flag in the index header for it (full-text indexes
don't use statistics).  However, I have not tested DBISAM with it turned
off, however.  That, and the major interface/documentation changes, is the
big hold-up to exposing it as an option in DBISAM.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

> It's something that has been asked for in DBISAM, and yes, it is possible
> to do.  There is actually a flag in the index header for it (full-text
> indexes don't use statistics).  However, I have not tested DBISAM with it
> turned off, however.  That, and the major interface/documentation changes,
> is the big hold-up to exposing it as an option in DBISAM.

Could drive me to do the changes ? IOW, I don´t know how complex it will be
but if it is just a flag and it is considerable "easy" to change (I have the
source code), why not ?

If I change this it will be on my own risk.

Eduardo

Eduardo,

<< Could drive me to do the changes ? IOW, I don´t know how complex it will
be but if it is just a flag and it is considerable "easy" to change (I have
the source code), why not ? >>

It's in the index definition stored in the index header:

  TIndexDefinition = packed record
     IndexName: String[MAX_INDEXNAME_SIZE];
     KeySize: Word;
     KeyForeign: Boolean;
     KeyForeignRule: Byte;
     KeyForeignTable: String[MAX_TABLENAME_SIZE];
     KeyDescending: Boolean;
     KeyCaseInsensitive: Boolean;
     KeyUnique: Boolean;
     KeyCompressionType: Byte;
     KeyFieldCount: Byte;
     KeyDescendingFields: TDescendingKeyFields;
     KeyFields: TKeyFields;
     NoKeyStatistics: Boolean;  <<<<<<<<<<<<<<<<<<<<<<<<
     IndexChanged: Boolean; { internal use only }
     RemoveIndex: Boolean; { internal use only }
     EmptySpace: array [1..250] of Byte;
  end;

You'll need to modify (at the very least) these procedures in dbisamen.pas:

procedure TDataCursor.AddPrimaryIndex(NewIndexDefinition: pIndexDefinition;
                                     SuppressKeyViolation: Boolean;
                                     SendProgress: Boolean;
                                     SendDataLost: Boolean);

procedure TDataCursor.AddSecondaryIndex(NewIndexDefinition:
pIndexDefinition;
                                       SuppressKeyViolation: Boolean;
                                       SendProgress: Boolean;
                                       SendDataLost: Boolean);

and add this code:

  NewIndexDefinition^.NoKeyStatistics:=True;

right before or after this code:

  NewIndexDefinition^.KeySize:=0;

in the procedures.  You'll also need to search out all instances of
"NoKeyStatistics" in dbisamen.pas and make the appropriate modifications (it
gets set to False during auto-primary key creation, for example).  And the
query/filter cost optimization checks need to be modified also, although
you'll see how we do that with the text indexes already.

Then, recompile DBISAM.  However, you'll have to drop and re-add all indexes
before the indexes will start working without the statistics.

And, I suspect that there may be some blow-ups in the code after this, also.
DBISAM was designed to not use statistics for text indexes only.

As I said, I'll see what I can do about getting this modification in place.
We've got some other customers that want this also.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

Thanks for your explanation

Eduardo

Tim

I did the changes you have shown to remove index statictics.

I am playing with a table with more than 2M records and some secondary
indexes.
The size of IDX was reduced (it is not my target but of course it is good).
The time
to reindex the table was reduced a lot.

I know the problem with scroll bars (three state) in dbgrid but it is
secondary to me.

Is there another problem or behavior change ?
IOW, I have applyed ranges, findkeys, checking record counts and it seems
ok.

Eduardo

Eduardo,

<< Is there another problem or behavior change ?  IOW, I have applyed
ranges, findkeys, checking record counts and it seems ok. >>

Did you check queries or filters ?  As I said in my last message, the
optimization code needs some changes in order to work correctly and ignore
trying to get I/O counts on indexes that don't have statistics.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

> Did you check queries or filters ?  As I said in my last message, the
> optimization code needs some changes in order to work correctly and ignore
> trying to get I/O counts on indexes that don't have statistics.
I checked queries but after analising filters I saw this behavior but I am
impressed with the time to reindex.

I only use RecordCount and the Recno property to positioning records but
does not matter what number I see.

BTW, I think I am changing inside a dangerous place. But so far it seems
very good for performance improvements.

Thanks

Eduardo

Eduardo,

<< I checked queries but after analising filters I saw this behavior but I
am impressed with the time to reindex. >>

Yes, I'm sure that it is pretty fast.

<< I only use RecordCount and the Recno property to positioning records but
does not matter what number I see. >>

That's mainly what you lose, besides the I/O cost optimizations for queries
and filters.

BTW, I think I am changing inside a dangerous place. But so far it seems
very good for performance improvements. >>

Yes, this type of thing can be dangerous.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

One more question:
Is there any performance changes without statistics in index ? I have
already noticed that the process of indexing is pretty fast. But what about
queries ? And filters ?

Eduardo