Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 9 of 9 total |
How to speed up the dates on Filtering? |
Fri, Mar 2 2007 11:12 AM | Permanent Link |
JoeReal | 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. |
Fri, Mar 2 2007 11:19 AM | Permanent Link |
"Jose Eduardo Helminsky" | 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 |
Fri, Mar 2 2007 12:30 PM | Permanent Link |
JoeReal | 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 |
Fri, Mar 2 2007 2:04 PM | Permanent Link |
JoeReal | 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. |
Fri, Mar 2 2007 2:26 PM | Permanent Link |
"Jose Eduardo Helminsky" | 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. |
Fri, Mar 2 2007 5:09 PM | Permanent Link |
Sam Davis | 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 |
Mon, Mar 5 2007 9:41 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Mar 5 2007 12:08 PM | Permanent Link |
"Jose Eduardo Helminsky" | 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 |
Mon, Mar 5 2007 3:19 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Eduardo,
<< This message should be addressed to Joe. >> Sorry about that. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |