BorlandTalk.com

Allan · Guest

I have an application that has a large, flat file table.
There are 64 columns in the table. The primary key
is an autoincrement field.
I have a secondary index that has a date, a
char and an byte field. The char field only ever equals
two values.

Here's the problem. The table has around 30k records in
it. Doing a date change on the date field that is also in the
secondary index is soooo slow. Updating 60 records can
take up to 2 minutes!!! I know it is related to table size
because I reduced the table to 5k records and the date
change routine speeded up by a factor of zillions.
Also altering the byte field that is in the secondary index has
the same problem of speed.
Inserting and deleting records is ok, it is just the editing of
the fields that are also in the secodnary index that cause the
probelm.

Can the secondary index speed be affected by the number
of columns in the table? Should I consider reducing the number
of columns and would this help? I thought the secondary index
maintained a list based on the primary key hence the number
of columns will not affect it's speed.

What could it be that can cause such a horrible performance
(besides a questionable table design):?

Using paradox and D7 on win2k pentium 3 800mhz 256mb ram

Bill Todd (TeamB) · Guest

How ary you performing the update? SQL? A TTable and a loop? SetRange?
It would help if you would show us the update code.

Is the database on a file server or on your local drive?

Any anti-virus software running?

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Allan · Guest

Hi Bill

Everything is on a local drive. There is anti virus
s/ware on the machine but I have already tried
disabling it to see if there was any difference.

I did have an SQL statement but changed everything
to work on a TTable to try and improve performance.
In testing this I isolated the table update code and then
tested the time of the remaining code. Everything worked
fine so it has led me to believe that the table update and
maintenance of the secondary index is what is causing
everything to run slow.
The portion of code that does the table update is:
// update new records on RunDate
for i:= 1 to table2.recordcount do
begin
table2.edit;
table2.FieldValues['RunOrder']:=0;
table2.FieldValues['RunDate']:=strtodate(TodaysRunDate);
table2.FieldValues['RunNo']:=TodaysRunNo;
table2.FieldValues['Status']:=6;
if table2.fieldvalues['Citydest']='AKL' then
table2.fieldvalues['To_courier']:='KIE'
else table2.fieldvalues['To_courier']:='CP';
table2.post;
table2.next;
end;
// load all records into table then resort runorder
table2.active:=false;
table2.filter:='RunDate = '+''''+TodaysRunDate+''''+' and RunNo =
'+''''+inttostr(TodaysRunNo)+'''';
table2.indexname:='CityRunIndx';
table2.active:=true;
for i:= 1 to table2.recordcount do
begin
table2.edit;
table2.FieldValues['RunOrder']:=i;
table2.post;
table2.next;
end;

The fields RunOrder and RunDate are the ones maintained
in the secondary index, "CityRunIndx". CityDest is the other
field in the secondary index but this was assigned when the
record was initially inserted into the table so does not get
changed here.

Table controls are disabled.

Looking at it again, when I do the RunOrder change
from 0 to i, this would change its location
in the table and maybe this is causing conflict? I suppose
I should really start at the "Last" record and work upwards,
this way the existing index of the table would always be in sync
with the changes.

"Bill Todd (TeamB)" <no (AT) no (DOT) com> wrote

Bill Todd (TeamB) · Guest

What are the actual field types and sizes of the columns in the
secondary index? For example, Paradox does not have a CHAR datatype so
I assume this column is type A with a size of 1.

Set the MaxBuffSize parameter in the BDE Administrator to 16384.

Updating the secondary index is going to take some time but it should
not be a lot. One thing that will help performance is to instantiate
the field objects for your TTable at design time and use them for
updates. Also, take the call to StrToDate out of the loop. For
example:

table2RunDate.AsDateTime := TodaysRunDateTime;

Replace the filter with a call to SetRange.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Allan · Guest

The fields for the secondary index and there order are:
RunDate is a Date
CityDest is an Alpha of size 4 (and only ever has two values)
RunOrder is a Short

Ill try your suggestions, thanks.

"Bill Todd (TeamB)" <no (AT) no (DOT) com> wrote