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 structure 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 necessary. 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 performs 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 departments tackled the smaller pieces first while the Sys

Page 5