I have a large 1.5 GB dbisam.DAT table with many data fields (about 100 at least), and
about 2.44 million records.

This is from an old app with DBISAM version 4.19 build using Delphi 7.

The table has an index named DTC and is a TDateTime field
I was experimenting on trying to improve performance and so I did a few exploratory coding
and here are the results:

Simple filter: DTC>='2006-01-01' and DTC<='2006-12-31'
results: 1825842 records, time:47 seconds, FilterOptimizeLevel: foFull

Simple filter: DTC=>'2006-06-01' and DTC<='2006-12-31'
results: 1207348 records, time:57 seconds, FilterOptimizeLevel: foFull

Then if I use a simple date range, it was instantaneous result, same records and 0 seconds:
MyTable.setrange([EncodeDate(2006,1,1)],[EncodedDate(2006,12,31)]
MyTable.setrange([EncodeDate(2006,6,1)],[EncodedDate(2006,12,31)]


Now if I use datafields that are string field type, and they have an index, they are also
very fast, when doing simple filters like

CompanyID>'TXY' and CompanyID<'TZZ'    
this executes in under 1 second, perhaps 2.56 seconds in the worst case depending on the
size of the datafield.

I tried reversing the order of tests to remove effects of buffering and get the same trend.

Shall I avoid filtering based on dates any way I can or is there a way to improve the
filtering on dates. I may have also missed out on some improvements of succeeding versions
because the application is so stable and in production, and we avoid wanting to upgrade a
working application with such huge database.


Any help would be appreciated.

Joe

> Simple filter: DTC>='2006-01-01' and DTC<='2006-12-31'
> results: 1825842 records, time:47 seconds, FilterOptimizeLevel: foFull

Have you tried DTC between '2006-01-01' and '2006-12-31' ?

Eduardo

Thanks. It is down to 12 seconds. A huge improvement, but still not as fast as SetRange.



"Jose Eduardo Helminsky" <contato@hpro.com.br> wrote:

Joe

> Simple filter: DTC>='2006-01-01' and DTC<='2006-12-31'
> results: 1825842 records, time:47 seconds, FilterOptimizeLevel: foFull

Have you tried DTC between '2006-01-01' and '2006-12-31' ?

Eduardo

This is weird:
(DTC between '2006-10-1' and '2006-12-31') took only 12 seconds

while inserting time took very very long:
(DTC between '2006-10-1' and '2006-12-31 11:00 PM') 27 seconds

I've run tests in reverse order, same results.


JoeReal <joereal@avso.com> wrote:

Thanks. It is down to 12 seconds. A huge improvement, but still not as fast as SetRange.

Joe

> Thanks. It is down to 12 seconds. A huge improvement, but still not as
> fast as SetRange.
Good. I think it will not be fast as SetRange.

JoeReal wrote:

> Thanks. It is down to 12 seconds. A huge improvement, but still not as fast as SetRange.
>
>
>
> "Jose Eduardo Helminsky" <contato@hpro.com.br> wrote:
>
> Joe
>
>
>>Simple filter: DTC>='2006-01-01' and DTC<='2006-12-31'
>>results: 1825842 records, time:47 seconds, FilterOptimizeLevel: foFull
>
>
> Have you tried DTC between '2006-01-01' and '2006-12-31' ?
>
> Eduardo
>
>

Joe,
    Have you tried using a different index, or no index at all? That
may speed things up.

Sam

Eduardo,

<< Thanks. It is down to 12 seconds. A huge improvement, but still not as
fast as SetRange. >>

It won't ever be as fast as SetRange.   SetRange implicitly doesn't have to
deal with building bitmaps to represent the WHERE condition, nor does it
have to deal with navigating them.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

> It won't ever be as fast as SetRange.   SetRange implicitly doesn't have
> to deal with building bitmaps to represent the WHERE condition, nor does
> it have to deal with navigating them.

This message should be addressed to Joe.
BTW, I know what you are talking about.

Eduardo

Eduardo,

<< This message should be addressed to Joe. >>

Sorry about that.

--
Tim Young
Elevate Software
www.elevatesoft.com