XPro User Group News (con’t)

stable when we went into production. We try to

maintain at least 1.5-2.0GB of free space on two of

the servers. This space allows us the flexibility to

move files around to make room for structure

changes, etc. We have had to change the struc-

ture on one or two of the big tables and have

managed to do it over a weekend. Reindexing has

been a bigger concern. One of our large files, an

account summary file, gets a number of special

tags put into the CDX each month, at month end,

to support end-of-month processing. When the

EOM processing is complete, we delete the tags.

Every other month we do a complete reindex of

the table to keep the CDX “bloat” under control.

The complete reindex takes about 26 hours.

Leaving all those tags there all the time caused an

undesirable slowdown in operations so the most

effective way to deal with the problem was to add

the additional tags only when needed and then

clear them out when they were no longer neces-

sary. Itwould be nice if FoxPro wasn’tsuch a

“sloppy” housekeeper in terms of deleted or

modified tags but that’s something we have to live

with. I am not certain that I can give you an across

the board answer on the search strategy without

doing a quick poll of the various teams. I know

that we started out using SQL selects to do much

of the searching and changed to SEEKs when we

discovered that most of our searching was for a

reasonably small result set. On small tables (less

than a couple hundred MB) SQL works Ok, even

for small result sets. When the table is large, with

a correspondingly large CDX, SQL dies and the

SEEK strategy provides much faster performance.

I will do a quick check with the teams that are

doing lookups and browses on the large tables and

will find out how they have optimized their

searches and displays. I know that they have got

it down to where the results are very impressive.

One recommendation I can make is “Buy memory,

buy lots of memory...” FoxPro loves it and per-

forms much better when you’ve got plenty avail-

able. Back in September we ran into a situation

where an SQL query would just die on a large

table. On a 400MB table the query would return in

several seconds but on a 500MB table it would

require an hour and a half. Adding more memory

resolved that issue. The irritating factor there was

that FoxPro doesn’t give you any notification that

Rushmore has been disabled due to lack of memory

like it does in a SEEK. We had to go out and add an

additional 4MB to over 300 workstations (the bill was

over $30,000). Thank God that we found out about it

a couple of weeks before the BIG, worldwide, memory

crunch when SlMMsdoubled or even tripled in price.

I think that, if we were budgeting for this all over again,

we would make the basic workstation at least 12MB,

maybe even 16MB. As it is, we are using a mixture of

8MB “basic” workstations and 16MB transaction

engines and heavy duty “query” machines. Another

recommendation has to do with CDX tags. Our initial

thoughts were to have a tag available for just about

any thing that anyone would want to search for. We

have since pared the CDX’s down to only those tags

that were absolutely necessary for operations. The

performance increase was quite impressive.

Fm: Dick Whetstone<< Well, _43_ programmers/

managers doing Fox puts even more perspective on

tn>> << the situation ! <s> >>Yes is certainly does. I

believe that of those 43 programmers, all but 5 or 6

were actively involved, at one time or another, with

some aspect of the main application. The problem

hasn’t been getting all of them to worktogether, it’s

been getting them to TALK to each other before

working on something. <grin>. I am working on a

message that describes our strategy a little more in

detail. Stay tuned. Are you going to come down the

conference that our user group (and FoxProAdvisor)

are sponsoring in May in Chesapeake, VA? Our

Director of Data Processing is doing a session that

describes our downsizing and the details of the project

I have been speaking of.

Fm: Dick Whetstone<< Would you mind sharing with

us the general strategy and concerns dealing>><<

with that many number of programmers involved in I

project? >>Not at all. First, my comment about 43

working on one project may have been a bit of an

exaggeration, but let me explain. We have nearly 50

programmers and managers in the 5 departments that

make up the Information Technology division. To add

an additional degree of complexity to the overall

picture,12-15 of these programmers are contract

employees from two information services companies.

About 3 years ago we embarked on a downsizing

project to move from a mainframe onto a LAN-based

system. There were numerous applications to be

downsized and every department had some “piece of

the pie”. Small teams within several of the depart-

ments tackled the smaller pieces first while the Sys-

Page 5